Hi,
Can anybody tell me why below code does NOT put the formula or its result in the selected cells? I can not see what I'm missing here, but I'm certainly are missing something.
What I want to do is based on the presence of a search string in a selected range of cells (all in the same column) add the word "STOCK") in a column offset (0,4) to the right on the same rows, and if not to leave the cell blank
If I put =IF(ISNUMBER(FIND("NON INVENTORY",B441)),"STOCK"," ") in any of the offset cells it works fine
The code also works with UserRange.Offset(0,4).Formula = "=" & UserRange(1, 1).Address(False, False
Your feedback will be greatly appreciated.
Can anybody tell me why below code does NOT put the formula or its result in the selected cells? I can not see what I'm missing here, but I'm certainly are missing something.
What I want to do is based on the presence of a search string in a selected range of cells (all in the same column) add the word "STOCK") in a column offset (0,4) to the right on the same rows, and if not to leave the cell blank
Code:
Sub SelUserRange()
Dim UserRange As Range
Dim TopCell As Range
Dim Bb, StrSrch, StrRepl As String
StrSrch = "NON INVENTORY:"
StrRepl = "STOCK"
Bb = ""
On Error GoTo Canceled
Set UserRange = Application.InputBox(Prompt:="Please Select Range of Items that need to be converted", Title:="Range Select", Type:=8)
UserRange.Offset(0, 4).Formula = "=IF(ISNUMBER(FIND(" & StrSrch & "," & UserRange(1, 1).Address(False, False).Value & ")}," & StrRepl & "," & Bb & ")"
Canceled:
End Sub
If I put =IF(ISNUMBER(FIND("NON INVENTORY",B441)),"STOCK"," ") in any of the offset cells it works fine
The code also works with UserRange.Offset(0,4).Formula = "=" & UserRange(1, 1).Address(False, False
Your feedback will be greatly appreciated.