PDA

View Full Version : Storing Variable From One Cell And Printing It Repetitively In Another Column



Ealcmhar
10-11-2012, 04:33 PM
Hi!

What I'm trying to accomplish is to grab the last cell with a value in column "a" and print it in cells within column "c" so long as the adjacent cell in column "b" isn't empty and the active cell is empty.

For instance:
----A------- -B -----------C
James ------Ruby ------Lowtown
Jim ---------Sapphire
Ferris -------Diamond
Middletown

"Middletown" being the last value in column "a" would be stored in the variable "nameH" and be printed in Column "C" three times, in the same rows as "Jim" and "Ferris", as their respective Column "C" cell are empty, while their respective Column "B" cell isn't empty.

I have what I could gather below:
Sub Test()
Dim nameH As String
nameH = Cells(Rows.Count, "a").End(xlUp)
For Each C In ActiveSheet.UsedRange.Cells
If B <> "" And C = "" Then C.Value = nameH
Next
End Sub

Any assistance would be appreciated.
Thanks!

Simon Lloyd
10-11-2012, 05:32 PM
Here's a different approach, it's not pretty but just knocked it up to show you another waySub fill_cells()
Dim MyString As String, Rng As Range, MyCell As Range
MyString = Range("A" & Rows.Count).End(xlUp).Value
Set Rng = Range(Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Address & ":C" & Range("A" & Rows.Count).End(xlUp).Row)
For Each MyCell In Rng
If MyCell.Offset(0, -1) <> "" Then
MyCell.Value = MyString
End If
Next MyCell
End Sub

Ealcmhar
10-11-2012, 07:49 PM
Thank you for that Simon, it works wonderfully! :content:

mikerickson
10-11-2012, 09:33 PM
Perhaps

With Range("C:C")
Application.Intersect(.EntireColumn, _
.SpecialCells(xlCellTypeBlanks).EntireRow, _
Range("B:B").SpecialCells(xlCellTypeConstants).EntireRow).Value _
= .Parent.Cells(Rows.Count, 1).End(xlUp).Value
End With