0

I'm new to excel VBA and although I've found it helpful in my work I'm currently stuck on merging a vlookup, Do While Loop and IF function. Basically I'm trying to write a Macro where the user inputs the name of two worksheets. From here I would like the Macro to take the value of "A2" in worksheet 1 and if it is found in a range in worksheet 2 (3 columns width with variable length (i.e. each week the length will be different) return "Yes" and if not return "No". I want this to keep going until cells in Column A of worksheet 1 contain no values. I keep on getting run time error 1004. The code I have come up with is below:

Sub CheckWorksheets()

Dim NewName As String
Dim NewName2 As String

NewName = InputBox("Name me?")

'Here I ask the user to input the name of the first spreadsheet (worksheet 1)

ActiveSheet.Name = NewName
If ActiveSheet.Index = Worksheets.Count Then
    Worksheets(1).Select
Else
    ActiveSheet.Next.Select
End If

'Here I toggle between the first and second worksheet

NewName2 = InputBox("Name me?")
ActiveSheet.Name = NewName2

'Here I ask the user to input the name of the second spreadsheet (worksheet 2)

ActiveWorkbook.Worksheets(NewName).Activate
Range("A1").Offset(1, 3).Select

'Here I have selected the cell where I would like the output of the below formula to be input

Do While Not IsEmpty(ActiveCell.Offset(0, -1))
    If ActiveCell.Offset(0, 3) = Application.WorksheetFunction.VLookup(ActiveCell.Offset(0, 3), ActiveWorkbook.Worksheets(NewName2).Range(("A2"), Range("A2").End(xlDown)), 1, False) Then

        'Run time error 1004 pops up as soon as this line of code has been run
        ActiveCell.Value = "Yes"
        ActiveCell.Offset(1, 0).Select
    Else
        ActiveCell.Value = "No"
        ActiveCell.Offset(1, 0).Select
    End If
Loop
End Sub

I've tried multiple variations from online sources but can't seem to get it working. Any help and an explanation to where I went wrong would be greatly appreciated. Thanks for your help!

Edit: Please see Worksheet1 and Worksheet2 for examples of the problem I'm hoping to solve (it's a basic example of what I'm looking to solve and I hope to apply it to other problems)

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
AndrewAC
  • 13
  • 4
  • Please format your code as such by indenting with 4 spaces. – Luuklag Sep 05 '17 at 07:11
  • Also try to avoid using select. That is probably the reason your code goes off. As select is dependent on which worksheet is visible, you should declare your cells worksheet specific: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Luuklag Sep 05 '17 at 07:14
  • Apologies Luuklag and thanks for the advice! – AndrewAC Sep 05 '17 at 07:19
  • 1
    try to step through your code and tell us on what line your error occurs. That makes debugging it easier. Also 1004 might indicate you are selecting a cell outside of the worksheet, which can happen with offset -1. So you also better avoid using offset. Go google a For Loop, and use that instead. – Luuklag Sep 05 '17 at 07:24
  • Can you post up some screenshots of the two spreadsheets with data... Not come across the use of Offset()... before but when I run the code, with ActiveCell.Offset(0, 3) assigned to a variable eg dim lookupValue = ActiveCell.Offset(0, 3), and then use lookupValue in Application.WorksheetFunction.VLookup(...), I don't get error 1004. – err1 Sep 05 '17 at 09:31
  • Images attached for review – AndrewAC Sep 05 '17 at 09:44
  • Hi Err1, have you had any success yet? Apologies, this is really doing my head in. I thought a vlookup between sheets would be easy but it's proving a lot more difficult than I thought – AndrewAC Sep 06 '17 at 11:07

0 Answers0