VBA Help - Using Variable in Formula - Excel 2016 for Mac

[Johnny Thunder]

Hello All,

Hitting a wall with the last piece of the puzzle in my code. I am trying to include a variable (Column Letter) in a formula and I am getting an error in the code.

Any help fixing this is appreciated!

Here is my code:

Option Explicit
'----------------------------------------------------------------------------------------
'--- Deletes unused formulas and applies print settings
'---------------------------------------------------------------------------------------
Sub CleanSheet()


Dim ws As Worksheet, ws2 As Worksheet
Dim wb1 As Workbook
Dim cell As Range, rng As Range, Header As Range
Dim Lastr As Long, LastR2 As Long
Dim CellCount As Double, FindString As String, rng2 As String, Result As String


'Application.ScreenUpdating = False
'Application.DisplayAlerts = False


'Declarations
Set ws = Sheets("Coversheet Trial Balance")
Set ws2 = Sheets("Drop In BW Raw Data")
LastR2 = ws2.Cells(Rows.Count, "B").End(xlUp).Row
FindString = "BALANCING (INTERCO)"
Set Header = ws2.Range("40:40")




'Defines how many rows to add
CellCount = Sheet4.Range("B42:B" & LastR2).Rows.Count


    'Defines Current Year Result Column
    For Each cell In Header
        If cell.Value = "2019" And cell.Offset(1, 0).Value = "Result" Then
            Result = Chr(cell.Column + 64)
            Result = "='Drop In BW Raw Data'!" & Result & "43"""    'The String Result is the letter I in this instance
        Else
    End If
        Next cell
       
    'Populates the formulas on the Trial Balance Sheet
    With ws
        .Range("B7").EntireRow.Offset(1).Resize(CellCount - 1).Insert Shift:=xlDown
        .Range("B8:E" & CellCount + 6).NumberFormat = "General"
        .Range("B8:B" & CellCount + 6).Formula = "='Drop In BW Raw Data'!B43"   'FS Description
        .Range("C8:D" & CellCount + 6).Formula = "='Drop In BW Raw Data'!E43"   'G/L Account
        .Range("E7").Formula = Result
        .Range("E7:E" & CellCount + 6).Formula = Result   '<-------------------------------Line that errors out----------------
        .Columns("E:E").NumberFormat = "_(#,##0.00_);_(#,##0.00);_(""-""??_);_(@_)"
        
         'searches all of column B for string
        Set rng = .Range("B:B").Find(What:=FindString, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
                
        'Populates the total Formula
        rng2 = rng.Address
        Lastr = ws.Cells(Rows.Count, "B").End(xlUp).Row
        .Range(rng2).Offset(0, 3).Formula = "=Sum(E7:E" & Lastr - 3 & ")"
    End With


End Sub

 

[JoeMo]

What's the error message, and what are the values of CellCount and Result when you get the error?

 

[Johnny Thunder]

Hey JoeMo, thanks for responding. So the error I am getting is a Runtime Error: 1004 "Method formula of Object Range Failed". CellCount results in "87" for how many rows need to be added. Thanks for the help.

 

[Fluff]

Try

            Result = "='Drop In BW Raw Data'!" & Result & "43"

 

[Johnny Thunder]

That worked perfectly! But honestly, I could have sworn I tried that yesterday. That was actually my first pass at the line and it errored out so I assumed it required more quotes. I also updated my CellCount Declaration from Double to long which seemed to fix another error that I would get occasionally "Runtime Error: 6' Overflow" which from some web searching said that the Double declaration can only hold so many numeric digits and that Long could hold billions.

Thanks for all the help!

 

[Fluff]

You're welcome & thanks for the feedback