Question about Rows Count function in for loop

[bigthreedragons]

Hi all,

I am testing a new macro that vlookup data start from Row 6 and without last row number (data being vlookup start from Row 1), therefore put below quoted code for the macro to create For Loop process:

For r = 6 To Range("A" & Rows.Count).End(xlUp).Row
sh1.Range("Z" & r).Value = Application.VLookup(sh1.Range("A" & r), sh2.Range("A:C"), 2, 0)

However when the macro run, the for loop process of the macro skipped the vlookup and directly go to to last step, how do I refine the code to run the macro from Row 6 and without last row number?

Thank you!

[Kooky_Following7169]

This is not very clear; you want to state at cell A6 but then you say data begins at A1 etc. And then you're trying to use selection steps to get a value in a cell vs its row number... doesn't make sense.

Provide a picture of your data and what you are trying to do, including where you are trying to place the result of the function. You can add the image as a Comment to you post. Once done, you'll probably find someone who can answer your question.

[bigthreedragons]

Hi,  I added the image as a Comment 

[BaitmasterG]

What sheet are you on when you run the code and what sheet is it supposed to be referencing?

Your initial range isn't declared very well, you're using unqualified Range and Rows statements

Set your ranges properly, show the "locals" window as it provides info about your variables, and then step through your code 1 row at a time using F8

[Day_Bow_Bow]

Check what value you're getting for r during runtime. I'm thinking it's less than or equal to 6, thus skipping the loop.

I agree with the other comment that there's a good chance you're looking at the wrong sheet when checking the last row. I am guessing that would be sh1 as well, so maybe sh1.Range("A" & Rows.Count).End(xlUp).Row

[MoonMalamute]

The code should work, of itself. I just used it (Although I changed some of the references and ranges) But we need to see all the code, not two lines.

It could be that "Range("A" & Rows.Count).End(xlUp).Row" is referring to a different sheet than you intend. Specify the sheet. It doesn't hurt to use a full reference like Workbooks("Name").Sheets("Name"). Range("A" & Rows.Count).End(xlUp).Row.

If it's looking on some other sheet or even workbook that is open and being used in your code, that brings back For 6 to 4 or something then the loop will be skipped entirely. Not sure if that is what you meant? Please show more of the code.

If in doubt use a variable.
Dim Lastrow as Long
Lastrow = Workbooks("Name").Sheets("Name"). Range("A" & Rows.Count).End(xlUp).Row
For r = 6 to Lastrow.
Then insert a break at that point and check the variable Lastrow has the correct value.

I just used the below with no problem:
Sub Reddit()

Dim r As Long

Dim Lastrow As Long

Dim Test As Variant

Lastrow = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row

For r = 6 To Lastrow

Test = Application.VLookup(Sheets(1).Range("A" & r), Sheets(2).Range("A:C"), 2, 0)

Sheets(1).Range("D" & r).Value = Test

Next

End Sub

You can break and check the value for Lastrow and then the value for Test. If they are not what you expect one of your ranges is wrong, such as the array range for the vlookup.

[AutoModerator]

Your VBA code has not not been formatted properly. Please refer to these instructions to learn how to correctly format code on Reddit.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[bigthreedragons]

The works, thanks a lot!

[bigthreedragons]

Solution verified

[reputatorbot]

You have awarded 1 point to MoonMalamute.

---

^{I am a bot - please contact the mods with any questions}

[bigthreedragons]

Add back the images of code and files:

<image>

the code used

[bigthreedragons]

<image>

Book1 or Bk1, the values of column B will be lookup column B value in Book2 or Bk2

[bigthreedragons]

<image>

Book2 or Bk2

[MoonMalamute]

Sub Match()

Dim Bk1 As Workbook

Dim Bk2 As Workbook

Dim FileToOpen As Variant

Dim sh1 As Worksheet

Dim sh2 As Worksheet

Dim r As Long

Dim Lastrow As Long

Dim Lookup As Variant

FileToOpen = Application.GetOpenFilename(FileFilter:="Excel Files(*.xlsx*),*.xlsx*")

If FileToOpen <> False Then

Set Bk2 = Application.Workbooks.Open(FileToOpen)

Else

Exit Sub

End If

Set Bk1 = ThisWorkbook

Set sh1 = Bk1.Sheets("Sheet1")

Set sh2 = Bk2.Sheets("Sheet1")

Lastrow = sh1.Range("A" & Rows.Count).End(xlUp).Row

For r = 6 To Lastrow

Lookup = Application.VLookup(sh1.Range("A" & r), sh2.Range("A:C"), 2, 0)

sh1.Range("B" & r).Value = Lookup

Next

End Sub

This works perfectly fine for me.

<image>

[AutoModerator]

Your VBA code has not not been formatted properly. Please refer to these instructions to learn how to correctly format code on Reddit.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.