help with xltoright & last col

[oddworld]

can somebody please advise me what i am doing wrong
my data lies in row 27 and row 28, and is updated monthly
i want the answer to be in "h20" (which is 8 rows above the data)
i can't figure out how to direct the macro to capture the last column to then apply the formula, i am also having drama's with" xltoright"
any advice would be appreciated
my macro so far seems to be capturing rows not columns and i am getting a circular ref as a result.

Sub seps_rate()
LastCol = Cells(8,Columns.Count).End(xlToRight).Columns
Range("h20").Formula = "=SUM( 28" & LastCol - 11 & ":28" & LastCol & ") / (((AVERAGE( 27t" & LastCol - 12 & ",27" & LastCol & ")+ SUM( 27" & LastCol - 1 & ":27 " & LastCol - 1 & "))/12)) "

End Sub

 

[jindon]

Hi

1) Isn't it xlToLeft?
2) Can you show us actual formula in the cell?

 

[HalfAce]

i am also having drama's with" xltoright"

Hello oddworld,
The bit Columns.Count is referring to (in present versions of excel) column IV (or column 256)
- and you can't go any further right from there.
I believe what you want to use is:
LastCol = Cells(8,Columns.Count).End(xlToLeft).Column

This will start in column 256 and work left to give you the column (number) of the last used
column in row 8.

 

[oddworld]

re

jindon in "h20" i have the following formula "=SUM(Q28:AB28)/(((AVERAGE(P27,AB27)+SUM(Q27:AA27))/12))"

 

[jindon]

Hi
try
this is assuming LastCol = 28 (AB)

Range("h20").Formula = "=SUM(" & Cells(28, LastCol -11).Resize(,12).Address & ") / (((AVERAGE(" & Cells(27, LastCol -12).Resize(,12).Address & ")+ SUM(" & Cells(27, LastCol - 11).Resize(,11).Address & "))/12)) "

 

[oddworld]

re

thanks jindon works a treat