I've written some code with the intention that on the worksheet "main" it will cycle through the rows,taking the value from the cell in column C (Reg) and then moving to the relevant tab depending on the beginning letter of the corresponding cell in column B. however sometimes (Reg) doesn't exist on any other tab and I can't work out how to handle it when it can't find (reg) in any of the other corresponding tabs.
I've tried to use on error goto next but that just ends up causing issues further into the Loop when it's pulling back values from cells that I didn't intend for it to look at. My VB is basic really so I'm not sure of how I could be dealing with this better as there is a lot of .select in my code and I've been told I shouldn't use that.
Sub comments_chase()
Dim comment, chase As String
Dim Reg As String
Dim reg_add As String
Dim Foundrange As String
Application.ScreenUpdating = False
Workbooks("Progression chases.xlsm").Activate
Worksheets("main").Activate
Range("C4").Select
Do Until ActiveCell.Value = ""
reg_add = ActiveCell.Address
Reg = ActiveCell.Value
ActiveCell.Offset(0, -1).Select
On Error GoTo Error_step
If Left(ActiveCell.Value, 1) = "L" Or Left(ActiveCell.Value, 1) = "M" Or Left(ActiveCell.Value, 1) = "N" Or Left(ActiveCell.Value, 1) = "O" Or Left(ActiveCell.Value, 1) = "P" Or Left(ActiveCell.Value, 1) = "Q" Then
Worksheets("Adie").Activate
Set Foundrange = Range("C:C").Find(Reg, LookAt:=xlWhole).Select
If Foundrange Is Nothing Then
GoTo Error_step
Else
ActiveCell.Offset(0, 11).Select
comment = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
chase = ActiveCell.Value
Workbooks("Progression chases.xlsm").Activate
Worksheets("main").Activate
Range("C:C").Find(Reg, LookAt:=xlWhole).Select
ActiveCell.Offset(0, 11).Select
ActiveCell.Value = comment
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = chase
End If
On Error GoTo Error_step
ElseIf Left(ActiveCell.Value, 1) = "A" Or Left(ActiveCell.Value, 1) = "B" Or Left(ActiveCell.Value, 1) = "C" Then
Worksheets("Andy").Activate
Set Foundrange = Range("C:C").Find(Reg, LookAt:=xlWhole).Select
If Foundrange Is Nothing Then
GoTo Error_step
Else
ActiveCell.Offset(0, 11).Select
comment = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
chase = ActiveCell.Value
Workbooks("Progression chases.xlsm").Activate
Worksheets("main").Activate
Range("C:C").Find(Reg, LookAt:=xlWhole).Select
ActiveCell.Offset(0, 11).Select
ActiveCell.Value = comment
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = chase
End If
On Error GoTo Error_step
ElseIf Left(ActiveCell.Value, 1) = "D" Or Left(ActiveCell.Value, 1) = "E" Or Left(ActiveCell.Value, 1) = "F" Or Left(ActiveCell.Value, 1) = "G" Or Left(ActiveCell.Value, 1) = "H" Or Left(ActiveCell.Value, 1) = "J" Or Left(ActiveCell.Value, 1) = "K" Then
Worksheets("Georgia").Activate
Set Foundrange = Range("C:C").Find(Reg, LookAt:=xlWhole).Select
If Foundrange Is Nothing Then
GoTo Error_step
Else
ActiveCell.Offset(0, 11).Select
comment = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
chase = ActiveCell.Value
Workbooks("Progression chases.xlsm").Activate
Worksheets("main").Activate
Range("C:C").Find(Reg, LookAt:=xlWhole).Select
ActiveCell.Offset(0, 11).Select
ActiveCell.Value = comment
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = chase
End If
On Error GoTo Error_step
ElseIf Left(ActiveCell.Value, 1) = "R" Or Left(ActiveCell.Value, 1) = "S" Or Left(ActiveCell.Value, 1) = "T" Or Left(ActiveCell.Value, 1) = "U" Or Left(ActiveCell.Value, 1) = "V" Or Left(ActiveCell.Value, 1) = "W" Or Left(ActiveCell.Value, 1) = "(" Or Left(ActiveCell.Value, 1) = "[" Or Left(ActiveCell.Value, 1) = "*" Then
Worksheets("Leona").Activate
Range("C:C").Find(Reg, LookAt:=xlWhole).Select
Foundrange = ActiveCell.Value
If Foundrange Is Nothing Then
GoTo Error_step
Else
ActiveCell.Offset(0, 11).Select
comment = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
chase = ActiveCell.Value
Workbooks("Progression chases.xlsm").Activate
Worksheets("main").Activate
Range("C:C").Find(Reg, LookAt:=xlWhole).Select
ActiveCell.Offset(0, 11).Select
ActiveCell.Value = comment
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = chase
End If
Error_step: Worksheets("main").Activate
Worksheets("main").Activate
Range(reg_add).Select
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub
The above code has 2 slightly different attempts to handle it. to show what I've tried
I'm getting runtime error 424 Object required and on debug it's stopping at "If Foundrange is Nothing Then"in the block referencing sheet ("Leona")
Ideally if it all worked correctly i would have hoped that it found the (Reg) on sheet ("Leona") and brought back the values in Comment and Chase and then put them into the relevant cell on sheet (Main)
can anyone help with where I've gone wrong?
