CODEWITHSUNDEEP
vbaexcel
Brandon Chiu
Brandon Chiu

Reputation: 3

Set Range as Found String Location in VBA

I'm trying to set a range in VBA as the range of the inputted string that I am looking for. The full procedure is supposed to pop up a dialog box to look for a specific string, find the string, create a range called location, set this range to the range of the string that was found, move a finite amount of columns to the right, and with that new columns value, print a string into that range.

The problem right now is that for some reason It is not setting the range to the range of the string it finds.

There is only one instance of the string throughout the workbook.

I'm also relatively new to VBA so there are something commands I don't know or understand how they work.

Sub test()
    Dim wb As Workbook
    Set wb = ActiveWorkbook
    Dim Inp As String
    Dim Loc As Range
    Dim Row As Integer
    Dim Col As Integer
    Dim NewLoc As Range
    Dim Sh As Worksheet

    Inp = InputBox("Scan ESD Tag")

    For Each Sh In ThisWorkbook.Worksheets
        With Sh.Columns("A")
            Set Loc = .Find(What:=Inp)
        End With
    Next

    Row = Loc.Row
    Col = Loc.Column + (3 * 5)
    Set NewLoc = Worksheets("Building 46").Cells(Row, Col)
    NewLoc.Value = "Over Here"

    Range("G2") = 6
End Sub

Upvotes: 0

Views: 218

Answers (1)

SJR
SJR

Reputation: 23081

Your problem is probably that your final block should be inside the loop as otherwise Loc is likely to be Nothing (unless the term is found on the last sheet) and your code will error. You should also check first that it is found to avoid such errors.

Sub test()

Dim wb As Workbook
Set wb = ActiveWorkbook
Dim Inp As String
Dim Loc As Range
Dim Row As Integer
Dim Col As Integer
Dim NewLoc As Range
Dim Sh As Worksheet

Inp = InputBox("Scan ESD Tag")

For Each Sh In ThisWorkbook.Worksheets
    With Sh.Columns("A")
        Set Loc = .Find(What:=Inp)
        If Not Loc Is Nothing Then
            Row = Loc.Row
            Col = Loc.Column + (3 * 5)
            Set NewLoc = Worksheets("Building 46").Cells(Row, Col)
            NewLoc.Value = "Over Here"
            Range("G2") = 6 'should specify a sheet here
            Exit Sub
        End If
    End With
Next

End Sub

Upvotes: 2

Related Questions