If cell 1 = "###" then cell 2 = "####" for CurrentRegion

[mockturtle29]

I'm trying to enter corresponding part numbers, and I keep getting a Type Mismatch error. The part number in column B corresponds to a part number to be entered in column B. I've hard-coded the numbers and included a couple of the loops here. I want it to go through the CurrentRegion because not every report has the same number of rows. This code works if I change the "For" to be "For row = 1 to 75", but I don't want to limit it to 75 rows.

Here is my code:

For Each row In ActiveCell.CurrentRegion.Cells
If Cells(row, 2) = "3038628" Then ' This is where it fails and gives the Type Mismatch error.
Cells(row, 1) = "88042-1"
End If
If Cells(row, 2) = "3072850-01" Then
Cells(row, 1) = "94977-1"
End If
Next row

I think I've got to change how it calls the cell, but I'm a VBA neophyte and am not sure how to change it. Any assistance would be appreciated!

Thank you in advance

 

[Dryver14]

what's the current region likely to be, just 2 cells?

 

[mockturtle29]

The current region is 16 columns and usually around 50 rows. I'd like to have it automatically go through all the active rows without limiting it to 75 rows and maybe having to change it in the future depending on the data in the report.

 

[Dryver14]

not sure I understand, is it going down one column comparing each second row with the row above it?

 

[mockturtle29]

It's not comparing anything. It's looking at the value in column two (B) and placing a value in column one (A) based on the value in column two. It works if I change the "For" line to "For row = 1 to 75". But I want it to look at all the active rows rather than limiting it to 75 rows.

 

[Dryver14]

Sub ChangeNumbers()

Dim I as long
Dim LastRow as long

LastRow = cells(rows.count,2).end(xlup).row

for I = 2 to lastrow

if cells(I,2)="3038628" then cells(I,1)="88042-1"
if cells(I,2) ="3072850-01" then cells(I,1)="94977-1"

next I

End Sub

 

[mockturtle29]

Thank you! This worked. I dropped the "Dim I as Long" declaration and just used "row" instead of "I" because I was getting a duplicate declaration error, and it worked like a charm. Here is my updated code that works. (Note that there are about 25 part numbers in my spreadsheet with more to come!)

Dim LastRow As Long
LastRow = Cells(Rows.Count, 2).End(xlUp).row
For row = 2 To LastRow
If Cells(row, 2) = "3038628" Then
Cells(row, 1) = "88042-1"
End If
If Cells(row, 2) = "3038627" Then
Cells(row, 1) = "88043-1"
End If
Next row

 

[Dryver14]

Glad it works for you.

 

[mockturtle29]

It's great! And I realized after all this that I was very unclear in my original post, so I apologize for the confusion. In my frustration after working on this for quite a while I accidentally said that the data in column B depends on column B when one of those should have been column A.