Announcement

Announcement Module
Collapse
No announcement yet.

VBA in Excel help

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts
  • Veteran
    • Apr 2002
    Pearll

    VBA in Excel help

    I need some help with some VBA coding in Excel. What I'm trying to do is compare values between two sheets in a workbook.

    I have the following columns:

    Sheet1, column A: Employee Names

    Sheet2, column A: Employee Names
    Sheet2, column B: Employee Numbers


    The code needs to read the list of Employee Names in Column A of Sheet1, search for matching names in Column A of Sheet2, and replace the Employee Name in Column A of Sheet1 with the corresponding Employee Number in Column B of Sheet2.


    To give an example:
    John Smith is in A2 of Sheet1.
    John Smith is also in A36 of Sheet2.
    His employee number (123456) is locate adjacent to his name in Sheet2 (B36)
    The value of A2 on Sheet1 is changed to that of his employee number, making it 123456.
  • Veteran
    • Aug 2001
    braincandy99

    #2
    Re: VBA in Excel help

    I don't have Excel in front of me but you basically need to do the following:

    A nested for/next loop if the number of items in the column doesn't change and is known. A nested Do/While loop otherwise.

    You want to use the strcomp type of command. There are a few different ones. A safe one is:

    if instr(1, Sheet1.cells(A2,x), Sheet2.cells(A2,y)) > 0 then
    'This means the match is found
    Sheet1.cells(A2,x) = Sheet2.cells(A3,y)
    else

    endif

    basically what the command above does is check 'in the string (instr)' starting at the specified character (1, start of string), of the specified string (Sheet1.cells(A2,x), where x is one of the loop variables) if the specified string (Sheet2.cells(A2,y), where y is the other loop variable) is present.

    Note I use 'A2' but you would have to put the actual column number.

    I can give more specifics tomorrow maybe, but that should get you close enough. There are other string comparison functions.

    cheers

    Comment

    • Registered Member
      • Mar 2006
      hoppabd

      #3
      Re: VBA in Excel help

      I always get these two screwed up - HLOOKUP and VLOOKUP. One of these two functions will do what you want.
      What ever doesn't make you stronger, kills you.

      Comment

      • Veteran
        • Aug 2001
        braincandy99

        #4
        Re: VBA in Excel help

        Here's an example.

        I put the sheet1 names in column 1, the sheet2 names in column1 and the sheet2 numbers in column 2.

        Code:
        Public Sub rearrange()
        
        s = Sheet1.Cells(1, 1)
        i = 1
        j = 1
        Do While s <> ""
            For j = 1 To 100  'arbitrary end point of your choice
                If Sheet2.Cells(j, 1) = "" Then Exit For 'exit if no name exists on sheet2
                If InStr(1, Sheet1.Cells(i, 1), Sheet2.Cells(j, 1)) > 0 Then
                    Sheet1.Cells(i, 2) = Sheet2.Cells(j, 2) 'i put the numbers in a different column for safety/checking
                    Exit For
                End If
            Next
            i = i + 1
            s = Sheet1.Cells(i, 1)
        Loop
        
        
        End Sub
        cheers

        Comment

        • Weird Store
          Snack Pervert
          • Dec 2003
          Tivia

          #5
          Re: VBA in Excel help

          I will confirm Braincandy's code. I saw this the other night but I was exhausted when I saw it. I know better than to write excel vba code when I am in that state of mind lol.

          Been a really long two weeks..phone company installed voip phones to replace the old analog while I was in the hospital. They managed to send an idiot who essentially crashed my network and then for unknown reasons deleted the dhcp tables because he couldn't figure out how to vlan tag his phones into the network. So although I had backups, I still had to learn how to configure their server and essentially reinstall the entire bloody phone system. Never mind the fact that I have never installed a phone system in my life as I am a server/network guy, not a phone guy lol. Anyhow, all done now but yeesh I likely worked 160+ hours in the past two weeks. Surprised I didn't put myself right back in the hospital.

          "Check out my youtube channel. Featuring League of Legends, FFXIV:ARR, gameplay and commentary."

          Comment

          • Veteran
            • Apr 2002
            Pearll

            #6
            Re: VBA in Excel help

            braincandy99 said:
            View Post
            Here's an example.
            I'm somewhat of a newb when it comes to VBA coding and have been learning along the way in order to complete this project. What sort of variable do I need to declare "s" as in your code?

            Comment

            • Veteran
              • Mar 2000
              Dragynphyre

              #7
              Re: VBA in Excel help

              I don't know VBA, I'd do it as a vlookup.

              Comment

              • Veteran
                • Apr 2002
                Pearll

                #8
                Re: VBA in Excel help

                Dragynphyre said:
                View Post
                I don't know VBA, I'd do it as a vlookup.
                I'm trying to do it all in VBA so that I can automate quite a bit of formatting that's needed after pasting data into it from another report. I've been able to delete columns, condense and total data for employees with multiple listings, remove duplicate listings, add and populate additional columns, etc. It's just this one part that has me stumped.

                Comment

                • Veteran
                  • Aug 2001
                  braincandy99

                  #9
                  Re: VBA in Excel help

                  in vba, declarations are not required. undeclared variables become variants.

                  in my code above you would declare the s as string since you know it will be and treat it as a string.

                  you would then, more safely, force the contents of the cell to be a string with something like s = str(sheet1.cells(1,1)).

                  cheers

                  Comment

                  • Veteran
                    • Apr 2002
                    Pearll

                    #10
                    Re: VBA in Excel help

                    Thank you! I got that all figured out using your code as a guide.

                    I'm using the following code to make a copy of a worksheet. Is there a way to copy values instead of formulas?

                    Code:
                    Sheets("Filtered").Copy Before:=Sheets(1)

                    Comment

                    • Veteran
                      • Aug 2001
                      braincandy99

                      #11
                      Re: VBA in Excel help

                      you might play with the macro record function and copy and paste (using paste special...values, instead of the normal paste).

                      I rarely combine VBA work and formulas in cells (unless I am using vba to put the formula in the cell)...

                      edit: I don't think you can paste an entire sheet using the paste special, so you would have to do it in two steps: 1. create a new sheet. 2. select all cells of the sheet you want to copy. 3. paste special into the new sheet.

                      Comment

                      • Veteran
                        • Apr 2002
                        Pearll

                        #12
                        Re: VBA in Excel help

                        braincandy99 said:
                        View Post
                        edit: I don't think you can paste an entire sheet using the paste special, so you would have to do it in two steps: 1. create a new sheet. 2. select all cells of the sheet you want to copy. 3. paste special into the new sheet.
                        That's what I was afraid of. I was hoping there was a cleaner way to do it than the recorded macro, but that will have to do.

                        Comment

                        Working...
                        X