I have a button on my form that exports an excel spreadsheet. I'm trying to add a bit of code to my VBA function to search a range of cells for data and if any cells have data, input a formula in the cell to the right. That formula would divide the cell with data by the cell at the bottom for a percentage. Can someone please tell me what I'm doing wrong?
I'm getting the subject error on the following line:
Code:
Set SrchRng = wks.Range("C2:AF" & Lrow).Cells '<-- THIS LINE
For Each Cel In SrchRng
If Cel.Value <> "" Then
Cel.Offset(0, 1).Formula = "=" & Cel.Address & "/" & wks.Cells(Lrow + 1, Cell.Column).Address
End If
Next
Here is the entire code:
Code:
Private Sub Command19_Click()'Export to Excel
Dim rs1 As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim cnt As Integer
Dim SrchRng As Range, Cel As Range
Dim Lrow As Long, Lrow1 As Long
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim rng As Excel.Range
Set db = CurrentDb
Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Add
Set wks = wbk.Worksheets(1)
Set rng = wks.Range("A2")
appExcel.Visible = False
cnt = 1
Set qdf = CurrentDb.QueryDefs("qry_Comparison_Bulk")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next
Set rs1 = qdf.OpenRecordset()
For Each fld In rs1.Fields
wks.Cells(1, cnt).Value = fld.Name
cnt = cnt + 1
Next fld
Call rng.CopyFromRecordset(rs1, 4000, 26)
qdf.Close
rs1.Close
Set rs1 = Nothing
Set qdf = Nothing
For Colx = 4 To 26 Step 2
Columns(Colx).Insert Shift:=xlToRight
Next
Set SrchRng = wks.Range("C2:AF" & Lrow).Cells
For Each Cel In SrchRng
If Cel.Value <> "" Then
Cel.Offset(0, 1).Formula = "=" & Cel.Address & "/" & wks.Cells(Lrow + 1, Cell.Column).Address
End If
Next
'Identifies the last row and row beneath it
Lrow = wks.Cells(Rows.Count, "A").End(xlUp).Row
Lrow1 = wks.Cells(Rows.Count, "A").End(xlUp).Row + 1
'Everything below is formatting
With wks.Range("A" & Lrow1, "AF" & Lrow1)
.Font.Bold = True
.Font.ColorIndex = 2
.Interior.ColorIndex = 16
.HorizontalAlignment = xlRight
End With
With wks.Range("C2:AE" & Lrow)
.NumberFormat = "0.000"
End With
wks.Cells(Lrow1, "C").Formula = "=SUM(C2:C" & Lrow & ")"
wks.Cells(Lrow1, "E").Formula = "=SUM(E2:E" & Lrow & ")"
wks.Cells(Lrow1, "G").Formula = "=SUM(G2:G" & Lrow & ")"
wks.Cells(Lrow1, "I").Formula = "=SUM(I2:I" & Lrow & ")"
wks.Cells(Lrow1, "K").Formula = "=SUM(K2:K" & Lrow & ")"
wks.Cells(Lrow1, "M").Formula = "=SUM(M2:M" & Lrow & ")"
wks.Cells(Lrow1, "O").Formula = "=SUM(O2:O" & Lrow & ")"
wks.Cells(Lrow1, "Q").Formula = "=SUM(Q2:Q" & Lrow & ")"
wks.Cells(Lrow1, "S").Formula = "=SUM(S2:S" & Lrow & ")"
wks.Cells(Lrow1, "U").Formula = "=SUM(U2:U" & Lrow & ")"
wks.Cells(Lrow1, "W").Formula = "=SUM(W2:W" & Lrow & ")"
wks.Cells(Lrow1, "Y").Formula = "=SUM(Y2:Y" & Lrow & ")"
wks.Cells(Lrow1, "AA").Formula = "=SUM(AA2:AA" & Lrow & ")"
wks.Cells(Lrow1, "AC").Formula = "=SUM(AC2:AC" & Lrow & ")"
wks.Cells(Lrow1, "AE").Formula = "=SUM(AE2:AE" & Lrow & ")"
wks.Cells(Lrow1, "B").Formula = "TOTAL (MG)"
With wks.Range("A1:AF1")
.Font.Bold = True
.Font.ColorIndex = 2
.Interior.ColorIndex = 16
.NumberFormat = "@"
.HorizontalAlignment = xlCenter
.EntireColumn.AutoFit
End With
appExcel.Visible = True
End Sub