CODEWITHSUNDEEP
vbaexcelexcel-2013worksheet-function
StarsFlyFree FromCozyNights
StarsFlyFree FromCozyNights

Reputation: 303

VBA To Perform Calculation

I am trying to use VBA to add a calculation to the used range of multiple worksheets. Problem is, I keep getting this error

'Type Mismatch'

on the line reading ws.Cells(countie, 12).FormulaR1C1 =...

Here is my syntax - what will fix this so this syntax will execute?

Function JunctionTest()
Dim ws As Worksheet, countie As Long
For Each ws In ActiveWorkbook.Worksheets
  With ws
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
    LastRow = .Cells.Find(What:="*",After:=.Range("A1"),LookAt:=xlPart, _
                  LookIn:=xlFormulas, SearchOrder:=xlByRows,SearchDirection:=xlPrevious, _
                  MatchCase:=False).Row
  Else
    LastRow = 1
  End If
  For countie = 1 To LastRow
        ws.Cells(countie, 12).FormulaR1C1 = "=RC7+RC8" / "=VLookup(A2, Totals!B2:R100, 3, False)"
Next countie
End Function

EDIT --

Download Sample Workbook - Garbagedata.xlsx

Upvotes: 0

Views: 521

Answers (1)

user6432984
user6432984

Reputation:

"=RC7+RC8" / "=VLookup(A2, Totals!B2:R100, 3, False)"

Remove the equal sign from "=VLookup(.

You can not mix R1C1 notation with A1 notation: RC7+RC8 & A2 don't mix

Instead of killing yourself trying to build a FormulaR1C1 in the VBA, get the formula working correctly on the worksheet and then print the working formula to the Immediate Window

enter image description here

UPDATE

ws.Cells(countie, 12).FormulaR1C1 = "=RC7+RC8/VLOOKUP(RC1, Totals!R2C2:R100C18, 3, FALSE)"

Sub JunctionTest()
    Dim ws As Worksheet
    Dim lastRow As Long
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            lastRow = .Range("B" & .Rows.Count).End(xlUp).Row
            .Range("L2:L" & lastRow).FormulaR1C1 = "=RC7+RC8/VLOOKUP(RC1, Totals!R2C2:R100C18, 3, FALSE)"
        End With
    Next
End Sub

Upvotes: 1

Related Questions