VBA - Index question

[Seandobson2402]

Hi All,

I'm new to the VBA world and tryng to learn as much as possible. I have been studying for a few weeks now and I have managed to get an ok understanding. However, I seem to be stuck on the following code: -

Sub PREMISE_ID_1()
Dim Index As Long
Index = 8

Do While Index < 99000
If Sheet2.Cells(24, 10) = "" Then
MsgBox "Please enter Premise ID"
Exit Sub
End If


If Sheet2.Cells(24, 10) = Sheet1.Cells(Index, 9).Value Then
'Premise Id
Sheet2.Cells(30, 6).Value = Sheet1.Cells(Index, 9).Value
'Postcode
Sheet2.Cells(32, 6).Value = Sheet1.Cells(Index, 18).Value
'Add1
Sheet2.Cells(34, 6).Value = Sheet1.Cells(Index, 14).Value
'Add2
Sheet2.Cells(36, 6).Value = Sheet1.Cells(Index, 15).Value
'Add3
Sheet2.Cells(38, 6).Value = Sheet1.Cells(Index, 16).Value
'Account Status
Sheet2.Cells(40, 6).Value = Sheet1.Cells(Index, 25).Value

End If


Index = Index + 1
Loop

End Sub


The sub routine takes the value entered on Sheet2 "J24" and does an index lookup on Sheet1 to populate information into Sheet2 "F" rows 30, 32, 34, 36, 38 & 40. If J24 is blank, I get a MsgBox saying "Please enter Premise ID".

I would like to add in another MsgBox. Where, if the information in Sheet2 "J24" doesnt match any data on Sheet1, a box will pop up saying "ID number not found". Is this possible?

Kind regards
Sean

 

[dmt32]

Hi,
Welcome to the Forum.
Try following update to your code which uses Range.Find method which generally is much faster than looping.

 Sub PREMISE_ID_1()

    Dim Search As String
    Dim FoundCell As Range
    
    Search = Sheet2.Cells(24, 10).Value
    
    If Len(Search) > 0 Then
        Set FoundCell = Sheet1.Columns(9).Find(Search, LookIn:=xlValues, lookat:=xlWhole)
        If Not FoundCell Is Nothing Then
            
'Premise Id
            Sheet2.Cells(30, 6).Value = FoundCell.Value
'Postcode
            Sheet2.Cells(32, 6).Value = FoundCell.Offset(, 9).Value
'Add1
            Sheet2.Cells(34, 6).Value = FoundCell.Offset(, 5).Value
'Add2
            Sheet2.Cells(36, 6).Value = FoundCell.Offset(, 6).Value
'Add3
            Sheet2.Cells(38, 6).Value = FoundCell.Offset(, 7).Value
'Account Status
            Sheet2.Cells(40, 6).Value = FoundCell.Offset(, 16).Value
            
        Else
            MsgBox Search & Chr(10) & "Record Not Found", 48, "Not Found"
        End If
        
    Else
        MsgBox "Please enter Premise ID", 48, "Entry Required"
        
    End If
    
End Sub

Object Variable FoundCell is Set to cell than matches Search value in Sheet1.Columns(9) This variable is used with Offset to return the values of all other required cells. You will need to check that the offset ranges are correct but you can update as required.

I have included the msgbox to report search value not found.

You can read more about Range.Find Method & Range.Offset in the VBA helpfile.


Hope Helpful

Dave

 

[Scott T]

Another option

Sub PREMISE_ID_1()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lr As Long
Dim mrow As Long
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
lr = ws1.Cells(Rows.Count, "I").End(xlUp).Row

 If ws2.Cells(24, 10) = "" Then
    MsgBox "Please enter Premise ID"
    Exit Sub
 End If
If Application.CountIf(ws1.Range("I8:I" & lr), ws2.Cells(24, 10)) = 0 Then
    MsgBox "ID number not found"
    Exit Sub
End If
mrow = Application.Match(ws2.Cells(24, 10), ws1.Range("I1:I" & lr))
'premise ID
ws2.Range("F30") = ws1.Range("I" & mrow)
'Postcode
ws2.Range("F32") = ws1.Range("R" & mrow)
'Add1
ws2.Range("F34") = ws1.Range("N" & mrow)
'Add2
ws2.Range("F36") = ws1.Range("O" & mrow)
'Add3
ws2.Range("F38") = ws1.Range("P" & mrow)
'Account Status
ws2.Range("F40") = ws1.Range("Y" & mrow)
End Sub