Search down a column until you find a blank cell in an adjacent column

Bagsy

Active Member
Joined
Feb 26, 2005
Messages
413
Office Version
  1. 365
Platform
  1. Windows
I wonder if anybody can help me a bit here, what I am trying to achieve is as follows
Search down column (“F5 : F”) until it finds a blank cell in column (“I5 : I”)

When it finds a blank cell I need to do this
Offset from the active cell in column (“F”) to the blank cell in column (“I”)
Format the font in the blank cell in column (“I “) to Red and also adjacent cell in column (“J”)
I then need to copy the value in (F) where the code has stopped and paste it at the end of column (B5 : B), then offset (0,2) from there and enter a zero.
Repeat above until all blank cells in column (F5 : F) have been found

This all sounds really simple when you write it down, normally I can sort of get things going to a point, but I am really struggling on this one.
There is probably so much wrong with this code but I cannot even get passed this line
VBA Code:
If rw.Columns ("I") = "" Then
I get the error “Run time Error object required”
Any help is as always very much appreciated
Code (if you can call it that) below
VBA Code:
Sub AddUsedStockCodes()

NumRows = Range("F5", Range("F5").End(xlDown)).Rows.Count ' Set numrows = number of rows of data.
    Range("F5").Select ' Select cell to start.
        For x = 1 To NumRows ' Establish "For" loop to loop "numrows" number of times.
If rw.Columns("I") = "" Then
    ActiveCell.Offset(0, 3).Select
     With Selection.Font
        .ThemeColor = vbRed
    ActiveCell.Offset(0, 1).Select
        With Selection.Font
            .ThemeColor = vbRed
    ActiveCell.Offset(0, -4).Select
                ActiveCell.Copy
                    Range("B5").Select
                Selection.End(xlDown).Offset(1, 0).Select
            ActiveCell.PasteSpecial xlPasteValues
       ActiveCell.Offset(0, 3).Select
    ActiveCell.Value = 0
End With
End With
End If
Next

End Sub
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,937
Office Version
  1. 365
Platform
  1. Windows
Are the blanks in col I actually empty, or do you contain a formula that returns ""
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,445
Office Version
  1. 2013
Platform
  1. Windows
Not sure if you intended the outcome of this:

VBA Code:
Sub Bagsy()

    Dim s As Range, d As Range, c As Range
    
    With ThisWorkbook.ActiveSheet       ' <<<< change to suit
        
        Set s = .Range("F5:F" & .Cells(.Rows.Count, "F").End(xlUp).Row)
        For Each c In s
            If Len(c.Offset(0, 3).Value) = 0 Then
                c.Offset(0, 3).Resize(1, 2).Font.Color = vbRed
                Set d = .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0)
                d.Value = c.Value
                d.Offset(0, 2).Value = 0
            End If
        Next c
    End With
End Sub
 
Solution

Bagsy

Active Member
Joined
Feb 26, 2005
Messages
413
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Are the blanks in col I actually empty, or do you contain a formula that returns ""
Hi Fluff thanks for helping, they are empty
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,937
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi Fluff thanks for helping, they are empty
Thanks for that, another option
VBA Code:
Sub bagsy()
   Dim Rng As Range
   For Each Rng In Range("I5:I" & Range("F" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks).Areas
      Rng.Resize(, 2).Font.Color = vbRed
      Range("B" & Rows.Count).End(xlUp).Resize(Rng.Count).Value = Rng.Offset(, -3).Value
   Next Rng
End Sub
 

Bagsy

Active Member
Joined
Feb 26, 2005
Messages
413
Office Version
  1. 365
Platform
  1. Windows
Not sure if you intended the outcome of this:

VBA Code:
Sub Bagsy()

    Dim s As Range, d As Range, c As Range
   
    With ThisWorkbook.ActiveSheet       ' <<<< change to suit
       
        Set s = .Range("F5:F" & .Cells(.Rows.Count, "F").End(xlUp).Row)
        For Each c In s
            If Len(c.Offset(0, 3).Value) = 0 Then
                c.Offset(0, 3).Resize(1, 2).Font.Color = vbRed
                Set d = .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0)
                d.Value = c.Value
                d.Offset(0, 2).Value = 0
            End If
        Next c
    End With
End Sub
Thank you so much GWteB, that is brilliant, works perfectly
 

Bagsy

Active Member
Joined
Feb 26, 2005
Messages
413
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, another option
VBA Code:
Sub bagsy()
   Dim Rng As Range
   For Each Rng In Range("I5:I" & Range("F" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks).Areas
      Rng.Resize(, 2).Font.Color = vbRed
      Range("B" & Rows.Count).End(xlUp).Resize(Rng.Count).Value = Rng.Offset(, -3).Value
   Next Rng
End Sub
Thanks for all your assistance Fluff on this and everything else, really appreciated.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,937
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,148,146
Messages
5,745,051
Members
423,917
Latest member
Frank1931

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