1

New to VBA, and I'm attempting to build a Macro that will loop through data that is using a Vlookup to fill in one cell that connects to a different Excel Sheet. I've successfully been able to get the Macro to work, however, it won't stop!

My goal is to take the data in column M of Excel #1 to Vlookup the name in the Excel #2 and fill in Excel #1's column C. Like I said, I was able to get this to work, however I can't seem to get my loop to stop when I reach a blank cell in column M.

Here is the VBA code I have right now. For reference Data starts at line 7.

Range("C7").Select

Do While Wroksheets("Sheet1").Range("M" & Rows.Count).End(xlDown).Row <> ""
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[10],'excel.xlsx'!Table46[[column1]:[column7]],7,FALSE)"
    ActiveCell.Offset(1, 0).Range("A1").Select

Loop

Thank you all for your help and time looking into this! I haven't had much luck taking what I've research and translated it into a successful loop.

Tried a bunch of different things located in different forum posts here and on other sites.

Could just be my lack of knowledge that isn't helping me translate it, but hoping to get a better understanding as this is something that will be useful for other items within the same macro once it's completed.

braX
  • 11,506
  • 5
  • 20
  • 33
ccolletti
  • 13
  • 3
  • `however, it won't stop` - because `.Row`, being the row number, is never `""`? – GSerg Mar 15 '23 at 14:52
  • Makes sense, I guess what should be there instead? I came across that expression while I was doing research and it had worked for them. – ccolletti Mar 15 '23 at 15:04

2 Answers2

0

Write Formulas to a Column Dynamically

Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")

Dim rg As Range

With ws.Range("C7")
    Dim lCell As Range: Set lCell = .EntireRow _
        .Resize(ws.Rows.Count - .Row + 1) _
        .Find("*", , xlFormulas, , xlByRows, xlPrevious)
    If lCell Is Nothing Then Exit Sub ' no data found
    Set rg = .Resize(lCell.Row - .Row + 1)
End With

rg.FormulaR1C1 _
    = "=VLOOKUP(RC[10],'excel.xlsx'!Table46[[column1]:[column7]],7,FALSE)"
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
  • Thank you for responding. I'm running into an issue with an error of "script out of range" in between the second and third lines. I put in the correct sheet name within my file and I guess it doesn't like that. Do you know of any issues within the coding if the sheet has more than one word and a "-"? – ccolletti Mar 15 '23 at 15:46
  • There is no special treatment, just double quotes e.g. `wb.Sheets("I Don't care what's my name")`. But the code may be in a different workbook, so you should reference the workbook with e.g. `Set wb = Workbooks("Whatever.xlsx")` or `Set wb = ActiveWorkbook`. – VBasic2008 Mar 15 '23 at 15:53
0

No need for a while loop here. The relative R1C1 formula allows you to write all formulas at once:

Dim ws As Worksheet
Set ws = Sheets("Sheet1")
  
Dim LastRow As Long
LastRow = ws.Cells(ws.Rows.Count, "M").End(xlUp).Row

With ws.Range("M1:M" & LastRow)
  .FormulaR1C1 = "=VLOOKUP(RC[10],'excel.xlsx'!Table46[[column1]:[column7]],7,FALSE)"
End With
leosch
  • 451
  • 2
  • 10
  • Thank you so much leosch! This worked, just one edit I had to make, the range for the column to run the vlookup in is C7:C. But other than that, works perfectly! Thanks you! – ccolletti Mar 15 '23 at 15:55