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
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
(1) re: If Worksheets(1).Columns(i).Formula <> "" Then
you're looking at a collection of variants ... so I'm not clear as to ehat
this is trying to achieve. If you are testing to see if the column has data,
theen use the COUNTA() function
(2)
Dim TNum(), Term2, Iput, Iput2 As String
Dim j, k As Integer
is wrong...
in VBA, if you don't explicitly state the data type, the variant is the
default.
You have DIM, by default is this:
Dim TNum() As Variant, Term2 As Variant, Iput As Variant, Iput2 As String
Dim j As Variant, k As Integer
I assume that you want
Dim TNum() As String, Term2 As String, Iput As String, Iput2 As String
Dim j As Integer, k As Integer
"carg1" wrote:
>
> 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")
> --------------------
>
>
> --
> carg1
> ------------------------------------------------------------------------
> carg1's Profile: http://www.excelforum.com/member.php...o&userid=15271
> View this thread: http://www.excelforum.com/showthread...hreadid=383784
>
>
Worksheets(1).Columns(i).Formula: A column does not have a formula. I assume
you are trying here to look in columns 2-7 for in the row you found in the
..Find method earlier in the code; if so I would do it this way (add a Dim
FoundCell as Range, FoundRow as Integer in the declarations):
Set FoundCell = Cells.Find(what:=Iput, LookIn:=xlFormulas, lookat:=xlWhole, _
searchorder:=xlByRows)
FoundRow = FoundCell.Row
....
Then, on the 'problem' line:
If Worksheets(1).Cells(FoundRow,i).Formula <>"" Then
....
Also, later in your code,
TNum(j) = Worksheets(1).Cells(FoundRow,i).Formula
Now a few things about the ReDim: ReDim will wipe out existing values held
in the array unless you ReDim Preserve. Also, if you use ReDim (1-6), it
does not index the array from 1 to 6; instead it does ReDim ("1 minus 6") and
you will get a 'subscript out of range' error for a subscript of -5)
I think that (with all taken into account) you will want it to read as
follows:
ReDim Preserve TNum(1 to j)
--
- K Dales
"carg1" wrote:
>
> 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")
> --------------------
>
>
> --
> carg1
> ------------------------------------------------------------------------
> carg1's Profile: http://www.excelforum.com/member.php...o&userid=15271
> View this thread: http://www.excelforum.com/showthread...hreadid=383784
>
>
Okay, you guys helped me immensely, your suggestions work great, but I'm having one more problem (that I sort of created for myself ). The problem is that I need to be able to output each of the values for items that have less than 6 values. I'm only 50% successful. I figured I needed a loop, so I declared an integer "l" and made this loop:
What it does, however, is display the values one at a time in the message box. Then, for lack of better words, doubles the amount of values shown. As in, if the values in the cells it looks at are 150 and 1783, I'll get 4 message boxes, the first saying "150,", I'll hit a button, the next says "1783,", hit the button, and two more will pop up, both containing "1783,".
I'm just trying to make the loop display each of the values with commas in between each. I'll have to figure a way to make it omit the comma on the last value.
PHP Code:
Sub Search() '
Dim TNum() As String, Term As String, Term2 As String, Iput As String, Iput2 As String Dim j As Integer, k As Integer, l As Integer Dim FoundCell As Range, FoundRow As Integer j = 0
Iput = InputBox("Please enter item:") 'Input Set FoundCell = Cells.Find(what:=Iput, LookIn:=xlFormulas, lookat:=xlWhole, _ searchorder:=xlByRows) FoundRow = FoundCell.Row
For i = 2 To 7
If Worksheets(1).Cells(FoundRow, i).Value <> "" Then j = j + 1
ReDim Preserve TNum(1 To j) TNum(j) = Worksheets(1).Cells(FoundRow, i).Value
End If Next i
For l = 0 To j Term2 = MsgBox("The values for this item are:" & vbCr & vbCr & _ TNum(j) & ",", vbYesNoCancel + vbDefaultButton2, "Values") Next l
Bookmarks