Adding formula to col A if col B has data

[andysh]

Hi,

I'm trying to add a formula into any cells in col A of a sheet where col B has data. I need the code to:

Look for last row in col B
If col B has data but A is empty
col A cell value = "Formula"

So in the below snip, the cells highlighted in yellow would be the ones where the formula is added.

They will be the last rows in each column, if it makes things easier. So basically the code would make both columns the same length by filling col A with the formula.

I did try this code but it only works if text is typed into cells, it doesn't work if multiple cells are pasted into col B.

If target.Column <> 2 Or target.Cells.Count > 1 Then Exit Sub

If target.Offset(0, -1) = "" Then
target.Offset(0, -1) = “TheFormula”

End If

That was more difficult to explain than I thought, so I hope it makes sense.

 

[Fluff]

How are you trying to run the code?

 

[andysh]

Preferably from a command button with some other code that pastes the data into col B.

The snippet of code above was in a worksheet_change sub on the sheet where the data is pasted but that appears to be the wrong approach?

 

[Fluff]

Ok, how about

Sub andysh()
   With Range(Range("A" & Rows.Count).End(xlUp).Offset(1), Range("B" & Rows.Count).End(xlUp).Offset(, -1))
      .FormulaR1C1 = "=rc[1]"
   End With
End Sub

 

[andysh]

That does pretty much exactly what I want, thanks.

How could I get it to do that on another sheet e.g.

With Workbooks("Other workbook").Worksheets("Sheet1")
With Range(Range("A" & Rows.Count).End(xlUp).Offset(1), Range("B" & Rows.Count).End(xlUp).Offset(, -1))
.FormulaR1C1 = "=rc[1]"
End With
End With

 

[Fluff]

Like

Sub andysh()
   With Workbooks("Other workbook").Worksheets("Sheet1")
      With .Range(.Range("A" & Rows.Count).End(xlUp).Offset(1), .Range("B" & Rows.Count).End(xlUp).Offset(, -1))
         .FormulaR1C1 = "=rc[1]"
      End With
   End With
End Sub

You just needed to add a full stop infront of each Range

 

[andysh]

****, when I was trying every permutation of that I missed the stop in front of the range in the first bracket. I had 'With .Range(Range'

Lesson learned, cheers Fluff

 

[Fluff]

You're welcome & thanks for the feedback