Method 'Range' of object '_Worksheet' failed; type mismatch error

[makeitwork09]

I'm using Excel 2007. I've looked through similar posts and I have tried the solutions for those posts with my own code, but am still getting the error.

I have several sheets that have different postions where range I want to sum starts and ends. Therefore, I created what I have below

Sub CalcPrice()
    Dim rRowFound As Range, ws As Worksheet, LastRow As Long, CFLastRow As Integer
    
    For Each ws In Worksheets
        If IsNumeric(ws.Name) Then
            Set rRowFound = ws.Columns(1).Find(what:="1", after:=ws.Range("A4"), LookIn:=xlValues, LookAt:= _
        xlPart, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
            
            If Not rRowFound Is Nothing Then
            
                LastRow = ws.Cells(Cells.Rows.Count, "E").End(xlUp).Row
                CFLastRow = LastRow
                
                ws.Range(ws.Cells(2, 8)).Formula = "=SUM(" & ws.Range("R" & rRowFound.Row, "R" & CFLastRow) & ")/G2*100"

            End If
        End If
    Next
End Sub

the line with the error is

ws.Range(ws.Cells(2, 8)).Formula = "=SUM(" & ws.Range("R" & rRowFound.Row, "R" & CFLastRow) & ")/G2*100"

I also tried it as

ws.Range(ws.Cells(2, 8)).Formula = "=SUM(" & ws.Range(ws.cells(rRowFound.row,18),ws.cells(CFLastRow,18 )) & ")/G2*100"

 

[combo]

Have you tested (Debug.print) what you try to assign with .Formula?
You need to add colon in range:
[tt]ws.Range(ws.Cells(2, 8)).Formula = "=SUM(" & ws.Range("R" & rRowFound.Row, "[!]:[/!]R" & CFLastRow) & ")/G2*100"[/tt]

combo

 

[PHV]

I'd change this:
LastRow = ws.Cells(Cells.Rows.Count, "E").End(xlUp).Row
with this:
LastRow = ws.Cells([!]ws.[/!]Cells.Rows.Count, "E").End(xlUp).Row

and this:
ws.Range(ws.Cells(2, 8)).Formula = "=SUM(" & ws.Range("R" & rRowFound.Row, "R" & CFLastRow) & ")/G2*100"
with this:
ws.Cells(2, 8)).Formula = "=SUM(" & ws.Range("R" & rRowFound.Row, "R" & CFLastRow) & ")/G2*100"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

How about the G2 reference? Two things:

What is the Sheet Reference?

If the value is ZERO, then what?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[makeitwork09]

@Skip, good looking out regarding a potential divide by 0 error. Just so I can try to figure out why I'm getting the type mismatch error I took your question regarding G2 into consideration. I have removed it from the code. I am still getting the error when I test the range portion of the code. The piece after sum.

I'll keep trying.

Thanks for the help all

 

[SkipVought]

[s]
ws.Range([/s]ws.Cells(2, 8)[s])[/s].Formula = "=SUM(" & ws.Range("R" & rRowFound.Row, "R" & CFLastRow)[b].address[/b] & ")

ws.Cells(2, 8).Formula = "=SUM(" & ws.Range("R" & rRowFound.Row, "R" & CFLastRow).Address & ")"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[makeitwork09]

@Skip, address did the trick
@PHV thank you, you were pretty close
@Common, thanks for trying to help

to all of you, thank you

 

[SkipVought]

Yet another possibility...

    ws.Cells(2, 8).Formula = "=SUM(R" & rRowFound.Row & ":" & "R" & CFLastRow & ")"

But a basic question.

Does it matter the FIRST and LAST rows in Column R?

If there are no numeric values ABOVE or BELOW your table, then why not sum the ENTIRE COLUMN?

If there are numeric values ABOVE, (maybe the formula is) then sum the FIRST row to the SHEET LAST ROW.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[makeitwork09]

@Skip, initially (last night) I did sum the whole column. Since I was getting the type mismatch error, from other responses to the same issue, I thought maybe because some of the cells were blank was why I was getting the error. By the time I posted this thread, I was (1) exhausted from trying to figure this out on my own and (2) what I posted was the state of the code by the time I surrendered.

Your help is always appreciated.

 

[SkipVought]

Since I was getting the type mismatch error, from other responses to the same issue, I thought maybe because some of the cells were blank was why I was getting the error.

If you can code a formula on a sheet, you can do the same thing in VBA. So the VERY FIRST THING you should do is to make sure it works on the sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[makeitwork09]

That is what I did first and the formula was working on the sheet, in it's original form. Because I'm still learning the code terminology it is harder for me to recognize the REAL issue. I knew it didn't make sense that the formula wasn't working, but I was running out steam, both because I could not figure out was wrong and because I was getting frustrated with not knowing, yet, the things you can recognize almost effortlessly, like adding address at the end.

Thanks

 

[makeitwork09]

Also, from reading threads on forums, I think, with good intentions of helping, sometimes you run into the blind leading the blind. Sadly, I feel into that trap and ended up trying some things that didn't make sense. Basically, I was trying and trying to figure out why I could not translate into code, what I knew was working in the sheet, to the point of exhaustion. I think as I work with this more and more, the solutions will become second nature. At least, I hope so.