If name is X insert Y phone number from Z worksheet in Excel
I am trying to create a simple macro or VBA that will insert a phone number in column Y for value of someones name in Column X from a directory worksheet.
So for example, worksheet 1 (directory) has names of individuals and phone numbers:
John Smith 123 456 789 Joe Garden 555 555 555 Jill Spill 999 999 999
When creating a new worksheet 2, when I put John Smith in Column A, I want it to automatically populate 123 456 789 in Column B.
Thanks for the help!
Try this code and let me know if there's anything that I missed. Be sure to change Sheet4 to the name of the sheet that has the phone # list.
Sub Macro12() Range("B1").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet4!C[-1]:C,2,FALSE)" ActiveCell.Offset(0, 1).Range("A1").Select End Sub
See also questions close to this topic
Formula to Adjust for Rounding Issues
I am trying to create a formula in Excel that solves for the base number of units needed on a monthly interval with an annual growth escalator that matches in whole units the total I'm starting with. I can't from a practical standpoint have fractions.
This is an example of the formula x is number of monthly units, y is the total number of units and the desired annual growth rate is 20%. Here is the algebra butin practice only one variable will need to be solved for
(x): y= 12x + (1.2x)*12+(1.2^2)*x*12+(1.2^3)*x*12. I get close using the round function but always end up a few short of the total desired due. Is there a way to add on the difference at the end formulaically?
I'm extremely close but I need to make sure the total by month matches the total desired.
Create Slide Per Picture
I have below macro that brings file from saved file from excel to PowerPoint what I need is to update the macro to bring one file per slide instead of bringing all into one slide
Sub CreatePagePerComment() Dim PowerPointApp As Object Dim myPPTX As Object Dim mySlide As Object Dim pptxNm As String Dim pptNm As Range Dim rSht As Worksheet Dim oSht As Worksheet Dim oPicture As Object Set pptNm = ThisWorkbook.Sheets("Sheet1").[PPTX_File] Sheets("Sheet1").[PPTX_File].Value = pptNm.Value CONFIRM_PPTX_APP: 'Create an Instance of PowerPoint On Error Resume Next 'Is PowerPoint already opened? Set PowerPointApp = GetObject(class:="PowerPoint.Application") 'Clear the error between errors Err.Clear 'If PowerPoint is not already open then open PowerPoint If PowerPointApp Is Nothing Then 'Set PowerPointApp = CreateObject(class:="PowerPoint.Application") With pptNm.Validation .Delete 'delete previous validation End With MsgBox "No PowerPoint file is open. Please open the PowerPoint file to where you " & _ "would like to export this table.", vbOKOnly + vbCritical, ThisWorkbook.Name Exit Sub End If 'Handle if the PowerPoint Application is not found If Err.Number = 429 Then MsgBox "PowerPoint could not be found, aborting." Exit Sub End If On Error GoTo 0 GET_PPTX_FILENAME: If pptNm.Value = "" Then MsgBox "Please select the PowerPoint file name, from the drop down list, to where you want to export the Headcount Review summary table." & _ Chr(10) & Chr(10) & "This Macro has selected the cell that contains a list of all open PowerPoint files. " & Chr(10) & Chr(10) & _ "If your file is not listed, please confirm it is open, then select any other cell, then return to this cell for " & _ "a refreshed file name list.", vbOKOnly + vbCritical, "No PowerPoint File Selected" pptNm.Select Exit Sub Else: If InStr(1, pptNm.Value, "ppt") > 0 Then pptxNm = pptNm.Value ElseIf InStr(1, pptNm.Value, "pptx") > 0 Then pptxNm = pptNm.Value & ".pptx" ElseIf InStr(1, pptNm.Value, "pptm") > 0 Then pptxNm = pptNm.Value & ".pptm" End If End If pptxNm = "NN Commitment Cards.pptm" Set myPPTX = PowerPointApp.Presentations(pptxNm) PowerPointApp.Visible = True PowerPointApp.Activate 'Adds second slide 'MsgBox SlideShowWindows(1).View.Slide.SlideIndex Dim Nm_shp As Shape, sld_no As Integer Dim pIndex As Integer, pName As String sld_no = myPPTX.Slides.Count pName = "Blue Transition" pIndex = 3 ADD_NEW_SLIDE: Dim SlideCnt As Integer Set mySlide = myPPTX.Slides.Add(sld_no + 1, 12) mySlide.Select mySlide.CustomLayout = myPPTX.Designs("N_PPTX_Theme").SlideMaster.CustomLayouts(pIndex) 'mySlide.Shapes.AddOLEObject Left:=10, Top:=10, Width:=(7.5 * 72), Height:=(10 * 72), ' Filename:=[B1].Value & "\" & [A132].Value & ".pdf", displayasicon:=msoFalse, link:=msoTrue For Each cel In [A3:A4] If Cells(cel.Row, [A1].Column).Value <> "" Then Set oPicture = mySlide.Shapes.AddPicture([B1].Value & "\" & cel.Value & ".png", _ msoFalse, msoTrue, Left:=10, Top:=10, Width:=(6 * 72), Height:=(7 * 72)) Set oSlide = myPPTX.Slides(1) With oPicture .Width = 7 * 72 .Height = 8 * 72 .PictureFormat.CropLeft = 0 .PictureFormat.CropTop = 0 .PictureFormat.CropRight = 0 .PictureFormat.CropBottom = oPicture.Height / 1.85 .Name = cel.Value .Line.Weight = 0.5 .Line.Visible = msoTrue .LockAspectRatio = msoTrue .Left = 1.5 * 72 .Top = 1.5 * 72 With myPPTX.PageSetup oPicture.Left = (.SlideWidth \ 2) - (oPicture.Width \ 2) oPicture.Top = (.SlideHeight \ 2) - (oPicture.Height \ 2) End With End With End If End Sub
What is the required tweak that I need to update in this macro to do so?
Extract data if date is within the range of dates. It should be able to extract multiple data if reference date matches multiple dates
I'm not sure how to properly phrase my question but here's an example:
In worksheet "B", I have
| Item A ---------- |--- DateStart |
| Item B----------- |--- Date End |
Basically, input, for example is: today()
Output would be ItemA if Startdate = today()
The problem is that the reference date is the start date. This only works if there's only a 1 day gap between the start and end date. It won't show the result if the activity lasts for multiple days.
So what do I do to extract the activity multiple times if reference date is between StartDate and EndDate?
Bonus question: is it me or using named ranges isn't helpful? I used a named range of A5:A400 and it didn't output the same result as using A5:A400 itself
In worksheet "A", i want to extract the activities we have for X date. I used the formula below. I'm not sure how it works but it works. It basically allows me to return multiple items if is not matches the date I want multiple times.
=INDEX($C$3:$C$8, SMALL(IF(ISNUMBER(MATCH($B$3:$B$8, $E$3, 0)), MATCH(ROW($B$3:$B$8), ROW($B$3:$B$8)), ""), ROWS($A$1:A1)))
Move a UserForm over a specific worksheet cell
I'm trying to move a userform partially off screen to reveal data in the activesheet below. A SpinButton click on the form then scrolls through a short list, highlighting cells needing attention).
I want to place the Userform.Top & .Left over a computed Cell.Top & .Left, to make the necessary data visible by moving the form
The UserForm.Move method seems NOT to be the correct method, despite its name and the fact that all its arguments are in Points, the same as Cell.Left and Cell.Top
My existing code calls UserForm_Activate() only when needed to reveal the worksheet table and to return to the normal default presentation. [Edit] I should have mentioned, the Activewindow can be offset both horizontally & vertically. Here is my code:
Private Sub UserForm_Activate() Dim AppXCenter As Long, AppYCenter As Long AppXCenter = Application.Left + (Application.Width / 2) AppYCenter = Application.Top + (Application.Height / 2) With Me .StartUpPosition = 0 'mode 1 not suitable when extending VBE to a 2nd monitor .Top = AppYCenter - (Me.Height / 2) .Left = AppXCenter - (Me.Width / 2) If .Top < 0 Then .Top = 0 If .Left < 0 Then .Left = 0 If UserForm_Needs_To_Move Then VBA.beep 'in lieu of a frustrated smiley 'I have tried many ways to calculate the offset to the desired column 'This is the simplest Me.Move [y1].Left - Me.Left 'NONE of them work!!! End If End With End Sub Private Sub UserForm_Initialize() 'UserForm_Activate 'is presently commented out 'the form currently appears on screen at first Activate event 'I have tried uncommenting, but it has not helped End Sub
I can get close to what I describe, but not properly accurately.
Does one have to use API GetDeviceCaps in Lib GDI32 etc, as per ref 2 ?
I want the code to work on different devices and resolutions etc as others will use the app.
How can I pass data between forms?
I have this form below:
As soon as I press the button "New Payment" I want the form to take the values inside the member ID text box, the first name text box and the last name text box and automatically load them to the fields respectively on the form below:
I'm thinking of doing it with global variables but I've never worked with VBA. Can someone explain to me how to do it?