Copy one cell to blank cells in a column, if another column has data-CodePudding

I am rather new at this but eager to learn :)

Unfortunately I have been struggling below below problem for the last few days.

I need to copy the value of a single cell (J2), to column A from cell A3 to the last needed depending on the last row with data in column B (this will be dynamic) but this should be true only where the cells are blank. In the attached example I need for J2 to be pasted in column A lines 11 to 15.

I have tried various methods but I cannot seem to combine copying a single value (not a range) only for blank cells in column A depending on whether there are data in column B.

Any ideas?

Thanks mates!

I have tried below code but it is not working.

Sub copyLC()

Range("I2").Copy Range("A3:A" & Range("B" & Rows.Count).End(xlUp).Row)

Range("B3:H" & Cells(Rows.Count, "B").End(3).Row).Copy Cells(Rows.Count, "B").End(3)(2)

Dim lastRow1 As Long
Dim lastRow2 As Long
Dim m As Long
Dim r As Long

lastRow1 = Sheets("equip_hlp").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("equip_hlp").Range("J3:J" & lastRow1).Copy

lastRow2 = Sheets("equip_hlp").Cells(Rows.Count, "I").End(xlUp).Row   1
Sheets("equip_hlp").Range("I" & lastRow2).PasteSpecial Paste:=xlPasteValues

Dim lastRow As Long
lastRow = Cells(Rows.Count, 2).End(xlUp).Row
Dim i As Long
For i = 3 To lastRow Step 1
    If Cells(i, 1).Value = "" And Cells(i, 2).Value <> "" Then Cells(i, 1).Value = Cells(2, "J").Value

Range("B3:H" & Cells(Rows.Count, "B").End(3).Row).Copy Cells(Rows.Count, "B").End(3)(2)

Dim lastRow1 As Long
Dim lastRow2 As Long
Dim m As Long
Dim r As Long

lastRow1 = Sheets("equip_hlp").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("equip_hlp").Range("K3:K" & lastRow1).Copy

lastRow2 = Sheets("equip_hlp").Cells(Rows.Count, "I").End(xlUp).Row   1
Sheets("equip_hlp").Range("I" & lastRow2).PasteSpecial Paste:=xlPasteValues

Dim lastRow As Long
lastRow = Cells(Rows.Count, 2).End(xlUp).Row
Dim i As Long
For i = 3 To lastRow Step 1
    If Cells(i, 1).Value = "" And Cells(i, 2).Value <> "" Then Cells(i, 1).Value = Cells(2, "J").Value


End Sub

CodePudding user response：

So you want to loop from rows 3 to lastRow for a single column, and where a cell is blank, you paste a value?

The below mock-up assumes you can actually find the last row (e.g., you do not have a blank last row in column B)

dim lastRow as long
lastRow = cells(rows.count, 2).end(xlup).row
dim i as long
for i = 3 to lastRow Step 1
    if cells(i,1).value = "" and cells(i,2).value <> "" then cells(i,1).value = cells(2,"J").value
next i

Hopefully this is a good starting place for you... dynamically finding the last row in based on column B (2).

I added a check in the If-statement for Column B to have data, but may not be necessary if you're working with a contiguous data set, hence the last Row using B.