C
carg1
Hey folks, hope you're all good tonight. I'm having a problem with a
type mismatch first and foremost, and need a little guidance on dynamic
arrays. This is a search macro. I want to find an item number, have it
display any values for the item (which will always be in columns B:G),
and then ask if any values need to be added or changed (haven't gotten
to that part yet). I was doing some preliminary testing, and the macro
stops in error at
Code:
--------------------
If Worksheets(1).Columns(i).Formula <> "" Then
--------------------
citing a type mismatch as the problem. I tried a few ways around this
using the Rows and Columns properties, trying Range, but I get errors
ranging from that to global class failures. I'm somewhat new to this,
using arrays and ifs anyway, so this is perplexing me and I'm sure its
something simple. Any suggestions?
PHP code:
--------------------
Sub Search()
Dim TNum(), Term2, Iput, Iput2 As String
Dim j, k As Integer
j = 0
Iput = InputBox("Please enter item:") 'Input
Cells.Find(what:=Iput, LookIn:=xlFormulas, lookat:=xlWhole, _
searchorder:=xlByRows).Activate 'Find it
For i = 2 To 7
If Worksheets(1).Columns(i).Formula <> "" Then
j = j + 1
ReDim TNum(1 - 6)
TNum(j) = Worksheets(1).Columns(i).Formula
End If
Next i
Term2 = MsgBox("The values for this item are:" & _
vbCrl & _
TNum(1) & ", " & TNum(2) & ", " & TNum(3) & ", " & _
TNum(4) & ", " & TNum(5) & ", " & TNum(6), _
vbYesNoCancel + vbDefaultButton2, "Tariff Numbers")
type mismatch first and foremost, and need a little guidance on dynamic
arrays. This is a search macro. I want to find an item number, have it
display any values for the item (which will always be in columns B:G),
and then ask if any values need to be added or changed (haven't gotten
to that part yet). I was doing some preliminary testing, and the macro
stops in error at
Code:
--------------------
If Worksheets(1).Columns(i).Formula <> "" Then
--------------------
citing a type mismatch as the problem. I tried a few ways around this
using the Rows and Columns properties, trying Range, but I get errors
ranging from that to global class failures. I'm somewhat new to this,
using arrays and ifs anyway, so this is perplexing me and I'm sure its
something simple. Any suggestions?
PHP code:
--------------------
Sub Search()
Dim TNum(), Term2, Iput, Iput2 As String
Dim j, k As Integer
j = 0
Iput = InputBox("Please enter item:") 'Input
Cells.Find(what:=Iput, LookIn:=xlFormulas, lookat:=xlWhole, _
searchorder:=xlByRows).Activate 'Find it
For i = 2 To 7
If Worksheets(1).Columns(i).Formula <> "" Then
j = j + 1
ReDim TNum(1 - 6)
TNum(j) = Worksheets(1).Columns(i).Formula
End If
Next i
Term2 = MsgBox("The values for this item are:" & _
vbCrl & _
TNum(1) & ", " & TNum(2) & ", " & TNum(3) & ", " & _
TNum(4) & ", " & TNum(5) & ", " & TNum(6), _
vbYesNoCancel + vbDefaultButton2, "Tariff Numbers")