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

--

Mikey

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?

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?

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,

Sbastien

'--------------------------------------------------------

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,

Sbastien

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

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

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?

--

Mickey

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?

--

Mickey

1. Which table design is better - more cols/less rows or less cols/more rows?

2. Which table design more is correct - more cols/less rows or less cols/more rows?

3. Find date duplicates Col 2 or Col 3 then combine Col 1 text

4. Sum values Col.A w/ indntcal values in Col.B, w/o naming Col.B

5. Sum values Col.A w/ indntcal values in Col.B, w/o naming Col.B val

6. Merging Col A and Col B in Col C including blank cells

7. Thanks Merging Col A and Col B in Col C including blank cells

8. find text from 1 col in 2nd col & return value from 3rd col

9. Trying to average col c if col a = "value" and col b = "value2"

10. Increase rows by 1 when moving data from col. to col.

11. How can I create a table w/ 5 cols. & 2/rows, then sort col 5 to

12. How insert a row into table with IDENTITY col with defined value for that col

13. Formula that returns Col A data in Col B, but omitting blank c

14. Formula to confirm entry in Col "C" is valid for data in Col "A"

15. Formula that returns Col A data in Col B, but omitting blank cells

5 post • Page:**1** of **1**