Need to fix break if not found running macro

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
1,970
Office Version
  1. 365
Platform
  1. Windows
Hi I need help running his if not found, If What:= is not found then do nothing. Right now lets say I run both if what I said is not found it breaks (debugs).

I would like for it not to break if its not found.

Do I need something like this On Error GoTo Done: ? Somewhere not sure. Any help thanks


Code:
Sub MoveRangeIfNotBlank250()
'Move value to other cell if next cell is empty'
    Dim Scol As Long
    Dim Dcol As Long
    Dim Rng As Range
    Dim Ofst As Long
    
    Scol = Rows(1).Find(What:="SALARY AMOUNT", After:=Range("A1"), _
        lookat:=xlWhole, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Column
    Dcol = Rows(1).Find(What:="HOURLY AMOUNT", After:=Range("A1"), _
        lookat:=xlWhole, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Column
    Ofst = Scol - Dcol
    For Each Rng In Range(Cells(2, Dcol), Cells(Rows.count, Dcol).End(xlUp))
      If Len(Rng.value) = 0 And Len(Rng.Offset(, Ofst).value) <> 0 Then
        Rng.value = Rng.Offset(, Ofst).value
        Rng.Offset(, Ofst).Clear
      End If
    Next Rng
 End Sub

Code:
 Sub MoveRangeIfNotBlank251()
'Move value to other cell if next cell is empty'
    Dim Scol As Long
    Dim Dcol As Long
    Dim Rng As Range
    Dim Ofst As Long
    
    Scol = Rows(1).Find(What:="HOURLY DAYS", After:=Range("A1"), _
        lookat:=xlWhole, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Column
    Dcol = Rows(1).Find(What:="HOURLY HOURS", After:=Range("A1"), _
        lookat:=xlWhole, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Column
    Ofst = Scol - Dcol
    For Each Rng In Range(Cells(2, Dcol), Cells(Rows.count, Dcol).End(xlUp))
      If Len(Rng.value) = 0 And Len(Rng.Offset(, Ofst).value) <> 0 Then
        Rng.value = Rng.Offset(, Ofst).value
        Rng.Offset(, Ofst).Clear
      End If
    Next Rng
 End Sub
 
Last edited:

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,172
Office Version
  1. 365
Platform
  1. Windows
Something like this
Code:
Sub MoveRangeIfNotBlank250()
'Move value to other cell if next cell is empty'
    Dim Srng As Range
    Dim Scol As Long
    Dim Drng As Range
    Dim Dcol As Long
    Dim Rng As Range
    Dim Ofst As Long
    
    Set Srng = Rows(1).Find(What:="SALARY AMOUNT", After:=Range("A1"), _
        lookat:=xlWhole, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        If Srng Is Nothing Then
            MsgBox "SALARY AMOUNT not found"
            Exit Sub
        End If
    Set Drng = Rows(1).Find(What:="HOURLY AMOUNT", After:=Range("A1"), _
        lookat:=xlWhole, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        If Drng Is Nothing Then
            MsgBox "HOURLY AMOUNT not found"
            Exit Sub
        End If
    Scol = Srng.Column
    Dcol = Drng.Column
    Ofst = Scol - Dcol
    For Each Rng In Range(Cells(2, Dcol), Cells(Rows.Count, Dcol).End(xlUp))
      If Len(Rng.Value) = 0 And Len(Rng.Offset(, Ofst).Value) <> 0 Then
        Rng.Value = Rng.Offset(, Ofst).Value
        Rng.Offset(, Ofst).Clear
      End If
    Next Rng
 End Sub
 

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
1,970
Office Version
  1. 365
Platform
  1. Windows
Hi thanks for your reply. I just tested it and it works perfect. I actually didn't think you had to add that much to do this, but what do I know.

Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,172
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.

You don't "Need" to add that much, for instance this
Code:
        If Srng Is Nothing Then
            MsgBox "SALARY AMOUNT not found"
            Exit Sub
        End If
could be reduced to this
Code:
        If Srng Is Nothing Then Exit Sub
But you wouldn't get any warning.
Also, rather than this
Code:
    Scol = Srng.Column
    Dcol = Drng.Column
    Ofst = Scol - Dcol
    For Each Rng In Range(Cells(2, Dcol), Cells(Rows.Count, Dcol).End(xlUp))
you could do
Code:
    Ofst = Srng.Column - Drng.Column
    For Each Rng In Range(Cells(2, Drng.Column), Cells(Rows.Count, Drng.Column).End(xlUp))
 

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
1,970
Office Version
  1. 365
Platform
  1. Windows
Gotcha I actually took out the msg box. I really don't need it or a warning, but I am going to just stick with the first response you gave me.

Thanks again
 

Watch MrExcel Video

Forum statistics

Threads
1,123,047
Messages
5,599,497
Members
414,315
Latest member
Yolanda5050

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top