Method Range of Object Global Failed

[Pam]

Hello:

I am trying to put in a formula based on when a particular cell is
empty, but
I get Metho Range of Object Failed. When debugging I get the row
number but it does not work foir me.

Here is a snippet of my code

If Cells(rwindex, X) = "" Then
Add_Sum (rwindex)
End If

'In the above logic if the rwindex is empty I will call the
Add_Sum(rwindex)
and pass in the rwindex that is empty.

Then I want to add a sum to a particular column on that row

Public Function Add_Sum(rwindex)


Dim Ac As String
Ac = "3G_Triage_Analysis"
workbook_name = HistoryDialog.Active_Workbook1
Windows(workbook_name).Activate

Sheets(Ac).Select
Range("AM " & rwindex & " ").Formula = "=SUM(AM2:AM & rwindex)"

'I will be doing this for columns AM2 to BJ2. I want to sum up
numbers in the column lets say from AM2 until there is no ID in
rwindex which is column A.
So rwindex will be the number row were I want the formula to do the
Sum

Is this legal, I can't think of any other way to get the value out of
rwindex.


End Function

I appreciate your help

Pam

 

[Jim Cone]

Two corrections...
1...
Dim workbook_name As String
workbook_name = "HistoryDialog.Active_Workbook1"
2...
Range("AM" & rwindex).Formula = "=SUM(AM2:AM" & rwindex - 1 & ")"
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Pam" <[email protected]>
wrote in message
Hello:
I am trying to put in a formula based on when a particular cell is
empty, but
I get Method Range of Object Failed. When debugging I get the row
number but it does not work foir me.
Here is a snippet of my code

If Cells(rwindex, X) = "" Then
Add_Sum (rwindex)
End If

'In the above logic if the rwindex is empty I will call the
Add_Sum(rwindex)
and pass in the rwindex that is empty.
Then I want to add a sum to a particular column on that row

Public Function Add_Sum(rwindex)
Dim Ac As String
Ac = "3G_Triage_Analysis"
workbook_name = HistoryDialog.Active_Workbook1
Windows(workbook_name).Activate

Sheets(Ac).Select
Range("AM " & rwindex & " ").Formula = "=SUM(AM2:AM & rwindex)"

'I will be doing this for columns AM2 to BJ2. I want to sum up
numbers in the column lets say from AM2 until there is no ID in
rwindex which is column A.
So rwindex will be the number row were I want the formula to do the
Sum
Is this legal, I can't think of any other way to get the value out of
rwindex.
End Function

I appreciate your help
Pam

 

[Pam]

Two corrections...
1...
Dim workbook_name As String
workbook_name = "HistoryDialog.Active_Workbook1"
2...
Range("AM" & rwindex).Formula = "=SUM(AM2:AM" & rwindex - 1 & ")"
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware

"Pam" <[email protected]>
wrote in message
Hello:
I am trying to put in a formula based on when a particular cell is
empty, but
I get Method Range of Object Failed. When debugging I get the row
number but it does not work foir me.
Here is a snippet of my code

If Cells(rwindex, X) = "" Then
Add_Sum (rwindex)
End If

'In the above logic if the rwindex is empty I will call the
Add_Sum(rwindex)
and pass in the rwindex that is empty.
Then I want to add a sum to a particular column on that row

Public Function Add_Sum(rwindex)
Dim Ac As String
Ac = "3G_Triage_Analysis"
workbook_name = HistoryDialog.Active_Workbook1
Windows(workbook_name).Activate

Sheets(Ac).Select
Range("AM " & rwindex & " ").Formula = "=SUM(AM2:AM & rwindex)"

'I will be doing this for columns AM2 to BJ2. I want to sum up
numbers in the column lets say from AM2 until there is no ID in
rwindex which is column A.
So rwindex will be the number row were I want the formula to do the
Sum
Is this legal, I can't think of any other way to get the value out of
rwindex.
End Function

I appreciate your help
Pam

Hello Jim Cone:

Thank you so much it worked! The only thing I had to do was increment
rwindex = rwindex + 1 to put the total in the row I wanted it to be
on.

Question what does the -1 do ? I did testing to try to figure it out
by change it to + 1 or even taking it out

Again Thank You, I was close but not on the money.

Pam

 

[Jim Cone]

Pam,

Re: why the minus 1 ...
On a worksheet, enter this equation in cell AM10 and see what happens.
= Sum(AM2:AM10)
--
Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Pam" <[email protected]>
wrote in message
Hello Jim Cone:
Thank you so much it worked! The only thing I had to do was increment
rwindex = rwindex + 1 to put the total in the row I wanted it to be
on.
Question what does the -1 do ? I did testing to try to figure it out
by change it to + 1 or even taking it out
Again Thank You, I was close but not on the money.
Pam

 

[Pam]

Pam,

Re: why the minus 1 ...
On a worksheet, enter this equation in cell AM10 and see what happens.
= Sum(AM2:AM10)
--
Regards,
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware

"Pam" <[email protected]>
wrote in message
Hello Jim Cone:
Thank you so much it worked! The only thing I had to do was increment
rwindex = rwindex + 1 to put the total in the row I wanted it to be
on.
Question what does the -1 do ? I did testing to try to figure it out
by change it to + 1 or even taking it out
Again Thank You, I was close but not on the money.
Pam

Jim:


Ok, another question for you. I tried everywhich way to maek this
work. My row is never the same because it depends on how many row are
in the spread sheet. Can you tell me what I amdoing wrong for this
range?


Range("AM:AQ" & rwindex) I am trying to get ranges Am through AQ:
I also tried
Range("AM & rwindex:AQ" & rwindex)
but it still does not work for me.



Thanking You in advance.
Pam

 

[Dave Peterson]

How about:

Range("AM" & rwindex & ":AQ" & rwindex)
or
range(cells(rwindex,"AM"),cells(rwindex,"AQ"))
or
range("AM" & rwindex).resize(1,5)
or
cells(rwindex,"AM").resize(1,5)

resize(x,y) means make the original range x rows by y columns.

 

[Pam]

How about:

Range("AM" & rwindex & ":AQ" & rwindex)
or
range(cells(rwindex,"AM"),cells(rwindex,"AQ"))
or
range("AM" & rwindex).resize(1,5)
or
cells(rwindex,"AM").resize(1,5)

resize(x,y) means make the original range x rows by y columns.








--

Dave Peterson- Hide quoted text -

- Show quoted text -

Hi Dave Peterson:


Range("AM" & rwindex & ":AQ" & rwindex) worked for what I was trying
to do. Thank you I tried every combination
I could think of but not the one you gave me. Thank you again.


Pam