Insert Matched Value

[scott]

I have a form that inserts an integer value in cell B2. LISTING 1 below
shows my Data Range of A2:B5. In LISTING 2, I'm trying make the sub
InsertMatch() loop through each cell in Column A until it finds a cell equal
to the new integer value. If a Column A cell is equal to the new integer
value, I need to insert it in the Column B cell that is 1 cell to the right
of the "matched" cell in Column A.

If no match is found, then just insert the new integer value into cell B2.
The Data Range of A2:B5 will always be growing because my form inserts a new
row at B before inserting a new value.

My InsertMatch sub is giving me a "Over Flow error". Can someone help me
figure out why my sub InsertMatch is giving this error?

So if this works, if a new integer value of 50 would be entered, it would be
inserted into cell B3.

Any help would be appreciated.


LISTING 1:

ColA ColB
45
50
60 60
75


LISTING 2:

Sub InsertMatch(iValue As Integer)

Dim c As Range, i As Integer

Set c = ActiveSheet.Range("B2")
iLastRow = getColumnLastRow("A")

For i = 2 To iLastRow

i = c.Row

If c.Offset(0, -1).Value = iValue Then
c.Value = iValue
Else
ActiveSheet.Range("B2") = iValue
End If

'set c to the next cell down
Set c = c.Offset(1, 0)

Next

End Sub

Function getColumnLastRow(sCol As String)

getColumnLastRow = ActiveSheet.Cells(Rows.Count, sCol).End(xlUp).Row

End Function

 

[Joel]

Sub InsertMatch(iValue As Integer)

Dim c As Range, i As Integer

iLastRow = getColumnLastRow("A")

For RowCount = 2 To iLastRow
With ActiveSheet
If .Range("A" & RowCount) = iValue Then
.Range("B" & RowCount) = iValue
Else
.Range("B2") = iValue
End If
End With
Next

End Sub

 

[scott]

I'm still getting the Overflow error with your code.

I'm using call InsertMatch("196000") in the command window.

 

[MP]

hint
what is the allowable range for an integer?

 

[scott]

I don't quite understand your question. Perhaps I should explain my problem
again. My data form will accept entries for ColA and/or ColB.

When the user enters a value in the form for either column and clicks ok, a
new row is inserted at row 2 and then the new values entered in the form are
inserted into row 2. I need someway of matching the columns that have
matching numbers, but leave a blank in either column next to a value that
has no matching value already entered.

So, my data range could end up looking like LISTING 2. ColA will always have
more entries, but either column can contain non-matching values at anytime.


LISTING 1:

ColA ColB
45
50
60 60
75


LISTING 2:

ColA ColB
45
35
50
60 60
75
90

 

[Joel]

when you declare a variable as an interger its range is -32,768 to 32,767.
Use Dim Long or Dim double for larger values.

 

[scott]

Dimming Long still gives error.

when you declare a variable as an interger its range is -32,768 to 32,767.
Use Dim Long or Dim double for larger values.

 

[Joel]

You also have a problem with comparing a string InsertMatch("196000") with a
number. do you want 19600 to be a string or a number? If it is a number
then remove the double quotes. You can use the val() VBA function to convert
a string to a number. Or use the format function(Range("A1"),"text") to
convert a number to a string.