Excel VBA - Fine Tuning Help...

[Groves22]

I run a report monthly, which I just created the following macro to run for me. The code is making the computer/Excel hang up for longer than it would when doing it by hand. I assume it's the section listed below.

Is there a better way to write some of this?

Thanks!

Dim t As Long

With P_Profile.Sheets(5)
    .Activate
    t = .Range("B3").End(xlDown).Row
    
    .Range("A3").Copy .Range("A3:A" & t)
    .Range("I3").Formula = "=VLOOKUP(VLOOKUP($A3&""-""&I$2,'[" & dYear & dMonth & dDay & " Sales - M.xls]Sheet1'!$B$2:$N$65536,11,FALSE),limit_lookup,2,FALSE)"
    .Range("I3").Copy .Range("N3:O3")
    .Range("I3").Copy .Range("Q3:T3")
    .Range("J3").Formula = "=VLOOKUP($A3&""-""&J$2,'[" & dYear & dMonth & dDay & " Sales - M.xls]Sheet1'!$B$2:$N$65536,12,FALSE)"
    .Range("K3").Formula = "=VLOOKUP($A3&""-""&J$2,'[" & dYear & dMonth & dDay & " Sales - M.xls]Sheet1'!$B$2:$N$65536,13,FALSE)"
    .Range("J3:K3").Copy .Range("L3:M3")
    .Range("P3").Formula = "=IF(ISERROR(VLOOKUP($A3&""-""&P$2,'[" & dYear & dMonth & dDay & " Sales - M.xls]Sheet1'!$B$2:$N$65536,12,FALSE)),""N"",""Y"")"
    .Range("I3:T3").Copy .Range("I3:T3", .Range("I3:T" & t))
    Calculate
    .Range("I3:T3", .Range("I3:T" & t)).Value = .Range("I3:T3", .Range("I3:T" & t)).Value
    .Columns("I:T").Replace What:="#N/A", Replacement:="N/A", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
    .Range("U3").Formula = "=SUMIF('[" & dYear & dMonth & dDay & " Sales - M.xls]Sheet1'!$C$2:$C$65536,A3,'[" & dYear & dMonth & dDay & " Sales - M.xls]Sheet1'!$O$2:$O$65536)"
    .Range("U3:v3").Copy .Range("U3:v3", .Range("U3:V" & t))
    Calculate
    .Range("U3", .Range("U" & t)).Value = .Range("U3", .Range("U" & t)).Value
    
    .Range("W3").Formula = "=COUNTIF(Drvr!$B$2:$B$65510,IMth!$C3&""-R-S"")"
    .Range("X3").Formula = "=COUNTIF(Drvr!$B$2:$B$65510,IMth!$C3&""-L-S"")"
    .Range("Y3").Formula = "=COUNTIF(Drvr!$B$2:$B$65510,IMth!$C3&""-E-S"")"
    .Range("W3:Y3").Copy .Range("W3:Y" & t)
    Calculate
    
    For ii = 3 To t
        If .Range("AZ" & ii).Value = "NULL" Or .Range("AZ" & ii).Value = "" Then
            .Range("AZ" & ii).Value = "N"
        Else
            .Range("AZ" & ii).Value = "Y"
        End If
    Next ii
        
'Update formulas for counts
    .Range("BJ3:DQ3").AutoFill Destination:=.Range(.Cells(3, 62), .Cells(t, 121))
End With

 

[SkipVought]

Hi,

Seems to me that this could be accomplished much easier, using MS Query to grab the new wrokbook data and return it to the sheet. MS query has a feature that automatically adjusts formulas in adjacent columns to the exactl length of data rows returned.

Short of that, if you set up your destination table as a Data > List > Create list, FIRST, with all your formulas in the first row of data, THEN paste in your data, ALL your formulas will propogate to fill the rows.

Third possibility, COPY the row of formulas and paste into the result range. ONE COPY/PASTE OPERATION.

Just another oservation. in the for ii = 3 to t loop, you are testin for "NULL". Do you ACTUALLY have the STRING equal to NULL or are you looking for a NULL value (often referred as empty)? What stuff DO you see in Column AZ?

Skip,

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

 

[SkipVought]

Also your calculate is calculating EVERYTING in EVERY workbook that is open in this instance of Excel.

Consider calculating a specific sheet or specific ranges in the sheet. Probably the latter.

Skip,

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

 

[Groves22]

Hey Skip...

Thanks for the quick response. I have never used MS Query, I will look into it!

About column AZ. When I pull the data from out system, I use a left-outer join of tables. So I will either see a date, a blank, or NULL (item doesn't exist in table). It's just the way our system is pulling the data.

Lastly, I didn't know you could calculate only certain sheets/ranges. How would I do that?

Thanks,
Josh

 

[SkipVought]

blank, or NULL (item doesn't exist in table).

"NULL" <> NULL

If the cell is empty then

[i]CellObject[/i].Value = ""

is TRUE

Lastly, I didn't know you could calculate only certain sheets/ranges. How would I do that?

[i]YourRangeObject[/i].Calculate

Check VB Help on Calculate.


Skip,

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

 

[SkipVought]

faq68-5829.

Skip,

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

 

[Groves22]

Thanks, Skip!

 

[Groves22]

Hey Skip...

I have MS Excel 2003. I can't find the MS Query tool in it. Is it just MS Excel 2007?

Thanks,
Josh

 

[Groves22]

Nevermind... I found it.