Reputation: 3
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
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