Getting Error messages on ranges

Question

I am getting error messages i that state that variable isn't defined. And VB is coloring this code red If LCase(wb.Range("Q" & i) = "y" Then .AutoFilter and I don't know why.

It's really important that only rows with a "y" in column Q in each range is pasted, and not everything else.

I had to change i to 2 To 500, and j = 2 To 20, but am worried that I might get columns that I don't want pasted into Sheet2(Materials_Estimate). I just want the range columns to be pasted.

The ranges include Sheet2 information as shown in the picture below (B=text, c=text, D=text, F=up to 3 numbers, G=a letter y, H=text, I=a calculation copied from Sheet 1 of the qty*cost)

Can anyone assist me?

[Code]

Option Explicit

Sub Estimating2() Application.ScreenUpdating = False

'naming the workbook and worksheets and ranges
Dim ProjectBudgeting1 As Workbook
Dim Materials_Budget As Worksheet
Dim Materials_Estimate As Worksheet
Dim LowesFax As Worksheet
Dim HomeDepotFax As Worksheet
Dim SBath1 As Range
Dim SBath2 As Range
Dim SBed1 As Range
Dim SBed2 As Range
Dim SBed3 As Range
Dim SBed4 As Range
Dim SHall As Range
Dim SFP As Range
Dim SRP As Range
Dim SKit As Range
Dim SGar As Range
Dim BuyOA As Range
Dim SFlorida As Range
Dim TargetRange As Range
Dim ActiveWorksheet As Worksheet


'naming the worksheets and ranges in code
Set ProjectBudgeting1 = ActiveWorkbook
Set Materials_Budget = Worksheets("Materials_Budget")
Set Materials_Estimate = Worksheets("Materials_Estimate")
Set LowesFax = Worksheets("LowesFax")
Set HomeDepotFax = Worksheets("HomeDepotFax")
Set SBath1 = Range("Materials_Budget!Supplies_Bathroom1")
Set SBath2 = Range("Materials_Budget!Supplies_Bathroom2")
Set SBed1 = Range("Materials_Budget!Supplies_Bedroom1")
Set SBed2 = Range("Materials_Budget!Supplies_Bedroom2")
Set SBed3 = Range("Materials_Budget!Supplies_Bedroom3")
Set SBed4 = Range("Materials_Budget!Supplies_Bedroom4")
Set SHall = Range("Materials_Budget!Supplies_Hallway")
Set SFP = Range("Materials_Budget!Supplies_FrontPorch")
Set SRP = Range("Materials_Budget!Supplies_RearPorch")
Set SKit = Range("Materials_Budget!Supplies_Kitchen")
Set SGar = Range("Materials_Budget!Supplies_Garage")
Set SFlorida = Range("Materials_Budget!Supplies_Florida")
 'Here I'm calling out the column q and looking for a "Y"
Set BuyOA = Range("Materials_Budget!Buy_OrderApproval")
'Here I'm naming the source of the information that gets copied into other sheets
Set ActiveWorksheet = Materials_Budget
'Here is the sheet where the source cells are pasted
Set TargetRange = Range("Materials_Estimate!EstimateTableArea1")

'Looking for the "Y" in column q for duplicating and printing corresponding rows (i) and columns (j)
For i = 12 To 520
        Cells("Q", i) = "Row " & i & " Col " & j
For j = 2 To 20
If LCase(wb.Range("Q" & i) = "y" Then .AutoFilter
i = i + 1
    Range("Q" & i).Select
    i = i - 1
Next q
Next i

    For j = 1 To 5
        Cells(i, j) = "Row " & i & "   Col " & j

End Sub

Application.ScreenUpdating = True

End With

End Sub

[Code/]

Change `Set SBath1 = Range("Materials_Budget!Supplies_Bathroom1")` to `Set SBath1 = Range("Supplies_Bathroom1")` and similarly for others. And not to mention that you dim those objects as `Range` — Siddharth Rout, Dec 21 '13 at 01:05

score 1 · Answer 1 · edited May 23 '17 at 10:25

1

I see many errors.

A) You have not declared your objects. For example, you need to declare SBath1, SBath2 etc.. as Range

B) You have declared ProjectBudgeting1 as workbook but then you are using it as a worksheet object.

C) When setting range, fully qualify them

D) Your wb object is undeclared. I would strongly suggest that you use Option Explicit at the top of your code

E) You have an extra bracket ) in wb.Range("Q12:Q" & LastRow))

F) Avoid the use of .Select INTERESTING READ

G) Finally, I would highly recommend on forgetting one word in vba and that is using End to stop a code. Reason is quite simple. It's like Switching your Computer using the POWER OFF button. The End statement stops code execution abruptly. Also the Object references held (if any) by other programs are invalidated.

Here is a basic gist on how your code should look like

Sub Estimating2()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng1 As Range, rng2 As Range

    Set wb = ActiveWorkbook '~~> Or ThisWorkbook?

    Set ws = wb.Sheets("Sheet1")

    With ws
        Set rng1 = .Range("Supplies_Bathroom1")
        Set rng2 = .Range("Supplies_Bathroom2")

        '
        '~~> And so on
        '

    End With
End Sub

edited May 23 '17 at 10:25

Community

1
1

answered Dec 21 '13 at 01:23

Siddharth Rout

147,039
17
206
250

I'm still getting error messages as the system looks through the code. – user3097333 Dec 21 '13 at 19:15
I used your code above but am getting a run time error 9 using: Set ws = ProjectBudgeting1.Sheets("Materials_Budget,Materials_Estimate,LowesFax,HomeDepotFax") Or I get a run time error 424 on using your statement as is Set ws = wb.Sheets("Sheets1") Can you assist? – user3097333 Dec 21 '13 at 20:52
`1` `ws` is a worksheet object. You cannot store multiple sheets in it. `2` Regarding "Set ws = wb.Sheets("Sheets1")", how have you defined `ws` and `wb`? – Siddharth Rout Dec 22 '13 at 00:07
See above, I had edited my code earlier today. The wb is set as a workbook Dim ProjectBudgeting1 As Workbook , and Set ProjectBudgeting1 = ActiveWorkbook , and ws are separately listed above Dim Materials_Budget As Worksheet , Set Materials_Budget = Worksheets("Materials_Budget") Set Materials_Estimate = Worksheets("Materials_Estimate") Set LowesFax = Worksheets("LowesFax") Set HomeDepotFax = Worksheets("HomeDepotFax") Dim Materials_Estimate As Worksheet Dim LowesFax As Worksheet Dim HomeDepotFax As Worksheet – user3097333 Dec 22 '13 at 04:37

Getting Error messages on ranges

1 Answers1