On Error Goto Blue Does not work

Hashiru

Active Member
Joined
May 29, 2011
Messages
286
Hi everyone, I have these lines of codes:
Code:
FoundValue = MySheet.Cells.Find(What:="Deductions from Net Pay", After:=Cells(1, 1), LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Row

On Error Resume Next
FoundValue1 = MySheet.Cells.Find(What:="Additions to Net Pay", After:=Cells(1, 1), LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Row
RowNum4 = FoundValue
        
                On Error GoTo Blue
                Do Until MySheet.Cells(RowNum4, 2) = "Total Deductions from Net Pay"
        
        
                   RowNum4 = RowNum4 + 1
                   CellContent = MySheet.Cells(RowNum4, 3)
                   
                   If CellContent = "401(K) Loan Repay" Then '1
                        TransferSheet.Cells(NextRow, 45) = MySheet.Cells(RowNum4, ColNum + 4) * -1
                    Else
                   End If
                    If CellContent = "Moneys Owed  to Company" Then '2
                        TransferSheet.Cells(NextRow, 46) = MySheet.Cells(RowNum4, ColNum + 4) * -1
                    Else
                   End If
                Loop
       
Blue:
           
          rownum5 = FoundValue1
          
          
          On Error GoTo Green
                Do Until MySheet.Cells(rownum5, 2) = "Total Additions to Net Pay"
        
        
           
                   rownum5 = rownum5 + 1
                   CellContent = MySheet.Cells(rownum5, 3)
                   
                   If CellContent = "Reimburse-Educational Assist" Then '1
                        TransferSheet.Cells(NextRow, 47) = MySheet.Cells(rownum5, ColNum + 4) * -1
                    Else
                   End If
                    'If CellContent = "Moneys Owed  to Company" Then '2
                        'TransferSheet.Cells(NextRow, 46) = MySheet.Cells(RowNum5, ColNum + 4) * -1
                    'Else
                   'End If
                Loop
           
2:
Green:
The code is supposed to find two phrase and use those phrases in the Do Until Loop to transfer records if found otherwise I want the code to do nothing.

Thanks in advance.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,177
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,177
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
The recommended way is to check if the value is found like this:
Code:
Dim rgFound as Range
Set rgFound = MySheet.Cells.Find(What:="Deductions from Net Pay", After:=Cells(1, 1), LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False)
If not rgFound Is nothing then 
   FoundValue = rgFound.Row
Else
   Exit Sub
End If
 

Watch MrExcel Video

Forum statistics

Threads
1,102,858
Messages
5,489,305
Members
407,686
Latest member
Chuck1960

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top