Rows & Cols - copy formulas in Col A to # rows in Col B

[Mikey]

I need to insert formulas in Col A along side unknown # rows of data in Col B
but can't seem to get the coding correct.
ie: Col B has continuous data in B3:B45 ; how do I enter a formula in A3
and using Range(Selection, Selection.End(xlToRight)).Select and/or
Range(Selection, Selection.End(xlDown)).Select
get the highlighted range to show A3:A45?
Using Range(Selection, Selection.Offset(0,-1)).Select give me 3 Cols
highlighted.
Suggestions?
Also what about when Col B doesn't have contiuous data in the rows? How do
I determine and highlight the appropriate number of rows in Col A?
Thanks

 

[JGeniti]

I'm a little confused as to what you are trying to do.
Do you only want to add the formula in the "A" column if there is data
in the "B" column? Can you be a little more specific or maybe give a
visual?

 

[sebastienm]

Hi, try something like:
'--------------------------------------------------------
Sub test()
Dim strCol As String 'column to apply formula to
Dim strColCheck As String 'column to check data size
Dim firstRow As Long ' first row of data
Dim strFormula As String ' formula for cell col=strCol row=firstRow
Dim lastCell As Range

'--- CHANGE HERE ---
firstRow = 3
strCol = "A"
strColCheck = "B"
strFormula = "=" & strColCheck & firstRow & "+ 4"
'eg: returns cell in B and add 4
'-------------------

Set lastCell = Range(strColCheck & 65536).End(xlUp)

If lastCell.Row < firstRow Then Exit Sub 'case no data in column to check

Range(Range(strCol & firstRow), _
Application.Intersect(Range(strCol & ":" & strCol), _
lastCell.EntireRow)).Formula = strFormula

End Sub
'---------------------------------------

Regards,
Sébastien

 

[Patti]

One way:



lstRow = Range("B" & Rows.Count).End(xlUp).Row



Range("A2").Formula = "= YOUR FORMULA IN QUOTES HERE"



Set sourceRange = Worksheets("YOUR SHEET NAME").Range("A2")

Set fillRange = Worksheets("YOUR SHEET NAME").Range("A2:A" & lstRow)

sourceRange.AutoFill Destination:=fillRange

'convert formula to value

Columns("A:A").Value = Columns("A:A").Value


HTH,

Patti

 

[Mikey]

Yes, that's correct. There may be from 5 to 500 rows depending on the data
and I usually enter the formula in A2, then copy it and hold the Shift button
down and then the End Down buttons to highlight all Rows in Cols A & B down
to the bottom of the data in Col B. Then I hit the left Arrow key and only
Cells A2:B??? are highlighted. I then paste the formula to these cells.
It's sort of a Copy, Move Over Right, Move Down to Bottom of data in next
Column, then Back to original Column and Paste. Is this enough of a picture?