Code help… (2000 SR-1)

Topic

New Reply

I’m sorry to be such a pain lately… I’m trying to write the following procedure and I’m not sure what the syntax for the commented lines would be… Can someone help me?… (Please let me know if you know of a better way to do this too…)

Sub GetTotals()

Dim rw As Integer, rwNew As Integer
Dim mySheet As Object, dirSheet As Object, tieinSheet As Object

Set mySheet = Worksheets(“ByDeptandProd”)
Set dirSheet = Worksheets(“DIRByDept”)
Set tieinSheet = Worksheets(“TieIn”)

rwNew = 2

For rw = 2 To 2000 ‘for each of the rows in Canadian Sheet
If Right(Trim(mySheet.Cells(rw, 2).Value), 5) = “Total” Then
tieinSheet.Cells(rwNew, 1) = mySheet.Cells(rw, 1)
tieinSheet.Cells(rwNew, 2) = mySheet.Cells(rw, 2)
tieinSheet.Cells(rwNew, 3) = mySheet.Cells(rw, 7)

‘Find matching value in column 2 of DirByDept sheet (ie… “001 Total” row on both sheets…)
‘If no match then
‘tieinSheet.Cells(rwNew,4) = 0
‘Else (there is a match)
‘tieinSheet.Cells(rwNew,4) = DirByDept row where the match is, column 8
‘End If

rwNew = rwNew + 1
End If
Next rw

Set mySheet = Nothing
Set dirSheet = Nothing
Set tieinSheet = Nothing

End Sub

Reply | Quote

Replies

Hello Trudi,

I would use the VLOOKUP worksheet function. You must avoid errors if there is no match.

Dim dblValue As Double

…

dblValue = 0
On Error Resume Next
dblValue = Application.WorksheetFunction.VLookup(Trim(mySheet.Cells(rw, 2).Value), _
dirSheet.Range(“B:H”), 7, False)
On Error GoTo 0
tieinSheet.Cells(rwNew, 4) = dblValue

You could also use MATCH and OFFSET.

Reply | Quote

Thanks a million Hans! …I thought of Match and Offset, but I wasn’t sure…

Your code works great… except that I forgot to deal with the changes in currency (column 1 on “ByDeptandProd” sheet)… so it finds the Canadian amounts when it has switched to USD… Oops… How would I do this with two conditions to match? For example… C$ 001 Total is different to U$ 001 Total…

BTW… You’re the best!

Reply | Quote

Is this like the spreadsheet you posted in the Access forum, i.e. currency is not repeated, or is the currency specified in every row?

Reply | Quote

Nope… Not the same one… Different project… (I’m not having a good week!! )

This is the workbook with 20 odd sheets and 1800 named ranges that I asked about before…
Remember I posted wanting to know if it was possible to use the Subtotal levels in code… I was supposed to post back with an example but didn’t get a chance to… I decided it was easier to just look at the sheets as a whole and find the Total lines that I needed…

I am pulling in the Currency from the line above each department total…
tieinSheet.Cells(rwNew, 1) = mySheet.Cells(rw – 1, 1) ‘gets currency from line above total

I’ll attach a sample workbook so that you can see what I am doing… It’s got dummy data and there are no company names or anything, so it will be okay…
I deleted all the sheets and code that weren’t relevant…. Hopefully you’ll be able to see right away what I’m trying to do… The “TieIn” sheet is what we’re talking about right now…

Thanks Hans!

Reply | Quote

Trudi

Just a note about your method of declaring variables from your first post in this thread:
———————————————————————————————
>>Dim rw As Integer, rwNew As Integer
>>Dim mySheet As Object, dirSheet As Object, tieinSheet As Object
———————————————————————————————-

You might want to look at Chip Person’s web site and see his article on declaring variables.
If I understand him correctly, when you group your declarations together, as you have,
only the first variable in your line is declared as the stated data type.
Hence, rw is an integer , while rwNew is variant. (not so good as it requires more overhead)

Others: Am I reading Chip correctly?

Paul

Reply | Quote

Yes, you are correct about the variables!

Reply | Quote

Yes, you are correct about the variables!

Reply | Quote

Not entirely correct.

Look at these two subs:

Option Explicit

Sub test()
    Dim a As Long, b As String, c As Double
    MsgBox TypeName(a)
    MsgBox TypeName(
    MsgBox TypeName©
End Sub
Sub test1()
    Dim a, b, c As Double
    MsgBox TypeName(a)
    MsgBox TypeName(
    MsgBox TypeName©
End Sub

When you run Test, you get three message boxes, stating:
Long, String and Double

When you run Test1, you get: Empty, Empty, Double.

This indicates the first two are Variants and the last one is (as expected) a double.

Reply | Quote

Not entirely correct.

Look at these two subs:

Option Explicit

Sub test()
    Dim a As Long, b As String, c As Double
    MsgBox TypeName(a)
    MsgBox TypeName(
    MsgBox TypeName©
End Sub
Sub test1()
    Dim a, b, c As Double
    MsgBox TypeName(a)
    MsgBox TypeName(
    MsgBox TypeName©
End Sub

When you run Test, you get three message boxes, stating:
Long, String and Double

When you run Test1, you get: Empty, Empty, Double.

This indicates the first two are Variants and the last one is (as expected) a double.

Reply | Quote

You are not reading it correctly.
Dim rw As Integer, rwNew As Integer
Dim mySheet As Object, dirSheet As Object, tieinSheet As Object

will work as expected and dim as desired.

Dim rw, rwNew As Integer
Dim mySheet, dirSheet, tieinSheet As Object

is what Chip refers to as not being correct. Only the variables with the “As” are given the type and are not variants. He does mention a “preference” for keeping them all on one line, but that is not a requirement.

Steve

Reply | Quote

Exactly what I thought… Thanks Steve…

Reply | Quote

Exactly what I thought… Thanks Steve…

Reply | Quote

Thanks for the clarification on this

Paul

Reply | Quote

Thanks for the clarification on this

Paul

Reply | Quote

Okay… I’m confused…

Check this link out… This is what I was taught… (using VB6)???
http://www.juicystudio.com/tutorial/vb/variables.asp%5B/url%5D
(go to the Declaring Variables part… )

Reply | Quote

The text in the link is correct.

Reply | Quote

The text in the link is correct.

Reply | Quote

Okay… I’m confused…

Check this link out… This is what I was taught… (using VB6)???
http://www.juicystudio.com/tutorial/vb/variables.asp%5B/url%5D
(go to the Declaring Variables part… )

Reply | Quote

Trudi

Just a note about your method of declaring variables from your first post in this thread:
———————————————————————————————
>>Dim rw As Integer, rwNew As Integer
>>Dim mySheet As Object, dirSheet As Object, tieinSheet As Object
———————————————————————————————-

You might want to look at Chip Person’s web site and see his article on declaring variables.
If I understand him correctly, when you group your declarations together, as you have,
only the first variable in your line is declared as the stated data type.
Hence, rw is an integer , while rwNew is variant. (not so good as it requires more overhead)

Others: Am I reading Chip correctly?

Paul

Reply | Quote

Trudi,

Now that I’ve taken a closer look at the code, I’m confused. The GetTotals procedure loops through the rows of the ByDeptAndProd sheet, and performs some actions only if column B contains “Total” (but not “Grand Total”). Why would you need to do a match on column B then? Your loop is already in the matching row, it seems to me, so you would only have to get the value from the correct column. Or am I missing it completely?

By the way, you can make your code more efficient by avoiding the .Select method where possible. For example,

Range(“A1:F1”).Font.Bold = False

is better than

Range(“A1:F1”).Select
Selection.Font.Bold = False

It won’t matter much if you select a range once, but if you keep on selecting different ranges in code, you pay a performance penalty.

You can also boost performance by not updating the display during code execution, but you should add that when your code is tested and true, not during development. Application.ScreenUpdating = False turns off updating, and Application.ScreenUpdating = True turns it on again.

Reply | Quote

Thanks for the tips Hans! I reallllllyyy do appreciate any and all advice…

I made the change to the code to add “and not Grand Total” because when I added the “row – 1” bit to get the Currency, I ended up getting “U$, Grand Total” on the bottom row of the data…. It was a quick fix, so it may not be right…

I am getting the right data from the first sheet… It’s the amounts that I want to pull in from the “DIRByDept” sheet that aren’t… If you look at the third sheet you’ll see that the spot for the US Amounts from that sheet are coming in as the Canadian amounts… repeated from earlier rows…. I BELIEVE, because the code you helped me with is finding the first match for “001 Totals”, etc and stopping there… I need to SOMEHOW tell this thing that it’s now needing to find the US amounts OR to find the second match for “001 Totals” etc… Am I making any sense??

Reply | Quote

VLOOKUP (as well as MATCH) will only find one hit.

It is used when you expect to get only one value. If there are multiple ones, you can either use the .find method to go thru column or “manually” (via VB) loop thru the list and compare each value to your current value and “do something” when it matches.

Steve

Reply | Quote

Okee dokee Steve…. Thanks!
Sounds like it’s time to resort to plain old control breaks…

Have a great day!

Reply | Quote

Okee dokee Steve…. Thanks!
Sounds like it’s time to resort to plain old control breaks…

Have a great day!

Reply | Quote

VLOOKUP (as well as MATCH) will only find one hit.

It is used when you expect to get only one value. If there are multiple ones, you can either use the .find method to go thru column or “manually” (via VB) loop thru the list and compare each value to your current value and “do something” when it matches.

Steve

Reply | Quote

Thanks for the tips Hans! I reallllllyyy do appreciate any and all advice…

I made the change to the code to add “and not Grand Total” because when I added the “row – 1” bit to get the Currency, I ended up getting “U$, Grand Total” on the bottom row of the data…. It was a quick fix, so it may not be right…

I am getting the right data from the first sheet… It’s the amounts that I want to pull in from the “DIRByDept” sheet that aren’t… If you look at the third sheet you’ll see that the spot for the US Amounts from that sheet are coming in as the Canadian amounts… repeated from earlier rows…. I BELIEVE, because the code you helped me with is finding the first match for “001 Totals”, etc and stopping there… I need to SOMEHOW tell this thing that it’s now needing to find the US amounts OR to find the second match for “001 Totals” etc… Am I making any sense??

Reply | Quote

Trudi,

Now that I’ve taken a closer look at the code, I’m confused. The GetTotals procedure loops through the rows of the ByDeptAndProd sheet, and performs some actions only if column B contains “Total” (but not “Grand Total”). Why would you need to do a match on column B then? Your loop is already in the matching row, it seems to me, so you would only have to get the value from the correct column. Or am I missing it completely?

By the way, you can make your code more efficient by avoiding the .Select method where possible. For example,

Range(“A1:F1”).Font.Bold = False

is better than

Range(“A1:F1”).Select
Selection.Font.Bold = False

It won’t matter much if you select a range once, but if you keep on selecting different ranges in code, you pay a performance penalty.

You can also boost performance by not updating the display during code execution, but you should add that when your code is tested and true, not during development. Application.ScreenUpdating = False turns off updating, and Application.ScreenUpdating = True turns it on again.

Reply | Quote

Nope… Not the same one… Different project… (I’m not having a good week!! )

This is the workbook with 20 odd sheets and 1800 named ranges that I asked about before…
Remember I posted wanting to know if it was possible to use the Subtotal levels in code… I was supposed to post back with an example but didn’t get a chance to… I decided it was easier to just look at the sheets as a whole and find the Total lines that I needed…

I am pulling in the Currency from the line above each department total…
tieinSheet.Cells(rwNew, 1) = mySheet.Cells(rw – 1, 1) ‘gets currency from line above total

I’ll attach a sample workbook so that you can see what I am doing… It’s got dummy data and there are no company names or anything, so it will be okay…
I deleted all the sheets and code that weren’t relevant…. Hopefully you’ll be able to see right away what I’m trying to do… The “TieIn” sheet is what we’re talking about right now…

Thanks Hans!

Reply | Quote

Is this like the spreadsheet you posted in the Access forum, i.e. currency is not repeated, or is the currency specified in every row?

Reply | Quote

Thanks a million Hans! …I thought of Match and Offset, but I wasn’t sure…

Your code works great… except that I forgot to deal with the changes in currency (column 1 on “ByDeptandProd” sheet)… so it finds the Canadian amounts when it has switched to USD… Oops… How would I do this with two conditions to match? For example… C$ 001 Total is different to U$ 001 Total…

BTW… You’re the best!

Reply | Quote

Hello Trudi,

I would use the VLOOKUP worksheet function. You must avoid errors if there is no match.

Dim dblValue As Double

…

dblValue = 0
On Error Resume Next
dblValue = Application.WorksheetFunction.VLookup(Trim(mySheet.Cells(rw, 2).Value), _
dirSheet.Range(“B:H”), 7, False)
On Error GoTo 0
tieinSheet.Cells(rwNew, 4) = dblValue

You could also use MATCH and OFFSET.

Reply | Quote