Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code help... (2000 SR-1)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code help... (2000 SR-1)

    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.

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code help... (2000 SR-1)

    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! <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code help... (2000 SR-1)

    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?

  5. #5
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code help... (2000 SR-1)

    Nope... Not the same one... Different project... (I'm not having a good week!! <img src=/S/sad.gif border=0 alt=sad width=15 height=15>)

    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!

  6. #6
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code help... (2000 SR-1)

    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

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code help... (2000 SR-1)

    Yes, you are correct about the variables!

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code help... (2000 SR-1)

    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.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code help... (2000 SR-1)

    Not entirely correct.

    Look at these two subs:

    <pre>Option Explicit

    Sub test()
    Dim a As Long, b As String, c As Double
    MsgBox TypeName(a)
    MsgBox TypeName([img]/forums/images/smilies/cool.gif[/img]
    MsgBox TypeNameŠ
    End Sub
    Sub test1()
    Dim a, b, c As Double
    MsgBox TypeName(a)
    MsgBox TypeName([img]/forums/images/smilies/cool.gif[/img]
    MsgBox TypeNameŠ
    End Sub

    </pre>



    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Code help... (2000 SR-1)

    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

  11. #11
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code help... (2000 SR-1)

    Okay... I'm confused...

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

  12. #12
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code help... (2000 SR-1)

    Exactly what I thought... Thanks Steve...

  13. #13
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code help... (2000 SR-1)

    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??

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code help... (2000 SR-1)

    The text in the link is correct.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Code help... (2000 SR-1)

    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •