Solved Help with VBA Macros??

October 24, 2016 at 12:39:19
Specs: Windows 7
Need help creating a macro to automatically search through a big list of users on one workbook and find those users in another workbook. Once the user is found in the second workbook the termination date and status needs to be put into columns next to the user in the first workbook.

This list has thousands of users.


Help??


See More: Help with VBA Macros??

Report •

✔ Best Answer
October 26, 2016 at 02:43:26
I think I understood the requirement try this code out.

Dim iExcel As Object

Sub LookUpStuff()
    
    Dim iFind As String
    Dim Bcell, CCell As Range
    
    Set iExcel = CreateObject("Excel.Application")
    Let iExcel.Visible = True
    
    iExcel.Workbooks.Open ("enter file path and file name here with extension")
    
    For Each Bcell In Range("A1", Range("A" & Rows.Count).End(xlUp))
        iFind = Bcell
            
            For Each CCell In iExcel.Worksheets("Sheet1").Range("O5", iExcel.Worksheets("Sheet1").Range("O" & iExcel.Worksheets("Sheet1").Rows.Count).End(xlUp))
                
                If CCell = Bcell Then
                    
                    Bcell.Offset(0, 1) = CCell.Offset(0, -1)
                    Bcell.Offset(0, 2) = CCell.Offset(0, -12)
                 
                End If
            Next CCell
    
    iFind = Empty
    Next Bcell
    
End Sub

message edited by AlwaysWillingToLearn



#1
October 24, 2016 at 13:43:05
In desperate need of help! Anybody?

Report •

#2
October 24, 2016 at 15:13:22
You may not need a Macro.
The VLOOKUP() function will work across two opened workbooks.

If you need help post a small sample of the two worksheets, but
first read this HOW-TO which explains the use of the < PRE > tags
to align your data:

http://www.computing.net/howtos/sho...

Please include Line Numbers and Column Letters.

MIKE

http://www.skeptic.com/


Report •

#3
October 24, 2016 at 17:48:06
re: "In desperate need of help! Anybody? "

Please note that this forum is made up of volunteers who answer questions as time allows. There is no need to submit a second post asking for help, especially in barely over an hour since your initial post.

In the future, please be a little more patient.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
October 25, 2016 at 01:55:50
Can you tell us how your workbook is layed out, this seems straight forward enough but we need to know layouts, if possible you can email me the workbook, PM me your email address and I can send you my email address.

As Derby said, we are all volunteers so can only help when we have time.


Report •

#5
October 25, 2016 at 03:14:14
Here is an example of how it can possibly work.

I have two workbooks

Workbook 1 (I called this one "db.xlsx")

          A        B     
1       Name1    1234
2       Name2    2234
3       Name3    2222
4       Name5    8978
5       Name6    0000

Workbook 2

           A             
1       Name1
3       Name3
5       Name6

in workbook 2 column A I enter the names that I want to find in workbook1, then I run the below code and it finds the entries and picks up the numbers in column B and puts them in column B of Workbook 2

Dim iExcel As Object

Sub LookUpStuff()
    
    Dim iFind As String
    Dim Bcell, CCell As Range
    
    Set iExcel = CreateObject("Excel.Application")
    Let iExcel.Visible = True
    
    iExcel.Workbooks.Open ("c:\......\db.xlsx")
    
    For Each Bcell In Range("A1", Range("A" & Rows.Count).End(xlUp))
        iFind = Bcell
            
            For Each CCell In iExcel.Worksheets("Sheet1").Range("A1", iExcel.Worksheets("Sheet1").Range("A" & iExcel.Worksheets("Sheet1").Rows.Count).End(xlUp))
                
                If CCell = Bcell Then
                    
                    Bcell.Offset(0, 1) = CCell.Offset(0, 1)
                End If
            Next CCell
    Next Bcell
    
End Sub

message edited by AlwaysWillingToLearn


Report •

#6
October 25, 2016 at 07:52:01
@AlwaysWillingToLearn: Thanks so much for getting back to me! My workbook 1 is a huge spreadsheet. Has over 1000's and 1000's or users. The headers begin in B4 and continue on to EE4. It contains roughly 6000 rows. Within my second workbook, this is where I copy a list of users that I am given (starting from A2) These users need to be found in work book one Column O5 (the 5 excludes the header). I then need Column N5 (where my terminated users are) for each user in my list to be pulled and plugged into Workbook 2 column B2. I also need Column C5 to be pulled for every user in my list aswell. Column C5 of workbook 1 needs to show up in column C2 of workbook 2.

Hope that makes sense!

Thanks so much!

message edited by kaygee


Report •

#7
October 25, 2016 at 07:52:46
@mmcconaghy Thanks for getting back to me! A macro is part of my requirements. But thank you!

message edited by kaygee


Report •

#8
October 25, 2016 at 07:55:20
@DerbyDad03: I'm so sorry! I started to panic I don't have much time for this.

message edited by kaygee


Report •

#9
October 25, 2016 at 08:24:21
A macro is part of my requirements.

Is this a homework assignment?

MIKE

http://www.skeptic.com/


Report •

#10
October 25, 2016 at 08:29:28
Sounds doable all that is required is a small modification to my original code, i'll do that tomorrow.

Report •

#11
October 25, 2016 at 09:27:33
@mmcconaghy Yes it is. Do you know if its possible to do a VLOOKUP and record it using the macro recorder? and do it that way? Sorry I'm not much of a macro wiz.

Report •

#12
October 25, 2016 at 09:29:33
@AlwaysWillingToLearn Awesome thank you so much. Also my workbook one can't be changed at all & the file name of this changes everyday. Will that affect anything? Also this needs to be able to work on multiple computers.

Report •

#13
October 25, 2016 at 10:36:41
Kaygee im not too sure what the rules are around homework help, i'll update the code but if a moderator jumps in before i give you the code then i'll have to refrain

Report •

#14
October 25, 2016 at 14:34:24
AlwaysWillingToLearn This isn't school homework. Its work homework! Just a task I have been assigned at work to help make our lives easier. Sorry for the confusion! But thanks tons! I appreciate it.

Report •

#15
October 26, 2016 at 02:43:26
✔ Best Answer
I think I understood the requirement try this code out.

Dim iExcel As Object

Sub LookUpStuff()
    
    Dim iFind As String
    Dim Bcell, CCell As Range
    
    Set iExcel = CreateObject("Excel.Application")
    Let iExcel.Visible = True
    
    iExcel.Workbooks.Open ("enter file path and file name here with extension")
    
    For Each Bcell In Range("A1", Range("A" & Rows.Count).End(xlUp))
        iFind = Bcell
            
            For Each CCell In iExcel.Worksheets("Sheet1").Range("O5", iExcel.Worksheets("Sheet1").Range("O" & iExcel.Worksheets("Sheet1").Rows.Count).End(xlUp))
                
                If CCell = Bcell Then
                    
                    Bcell.Offset(0, 1) = CCell.Offset(0, -1)
                    Bcell.Offset(0, 2) = CCell.Offset(0, -12)
                 
                End If
            Next CCell
    
    iFind = Empty
    Next Bcell
    
End Sub

message edited by AlwaysWillingToLearn


Report •

#16
October 26, 2016 at 07:31:39
This is awesome thanks! Is there a way to make it so I don't have to put the file path of of the file but instead just the name? So for example when I have the other workbook open the workbook with the macro in it automatically recognizes it by name?

Report •

#17
October 26, 2016 at 07:56:19
I also keep getting an error for this when I do use the file path of my workbook.

Report •

#18
October 26, 2016 at 08:45:13
Ok look at the code i have added a string

Enter file path and file name here with extension

Just enter the location of the file
The name of the file and the extension

Eg

C:\kaygee\desktop\db.xlsx

This will then always open this file

Is this what you were after?

Please note that the error you were recieving was because i had my file path in the code, modify that and bob will be your uncle


Report •

#19
October 26, 2016 at 08:58:02
Is it possible to have my workbook already open and have my code just access it by name? For example just have "db" instead of C:\kaygee\desktop\db.xlsx The reason for this is that this is going to be used my multiple users not only myself. so each filepath of this file with be different each time. Not sure if that makes sense.

Report •

#20
October 26, 2016 at 09:01:47
Thanks so much for getting back to me! My workbook 1 is a huge spreadsheet. Has over 1000's and 1000's or users. The headers begin in B4 and continue on to EE4. It contains roughly 6000 rows. Within my second workbook, this is where I copy a list of users that I am given (starting from A2) These users need to be found in work book one Column O5 (the 5 excludes the header). "I then need Column N5 (where my terminated users are) for each user in my list to be pulled and plugged into Workbook 2 column B2. I also need Column C5 to be pulled for every user in my list aswell. Column C5 of workbook 1 needs to show up in column C2 of workbook 2."

I re-copied what I wrote earlier. Does this code also do what I have put in quotations and bolded above? Sorry and thank you so much for your help.


Report •

#21
October 26, 2016 at 09:06:04
I also receive an error at this line of the code:

For Each CCell In iExcel.Worksheets("Sheet1").Range("O5", iExcel.Worksheets("Sheet1").Range("O" & iExcel.Worksheets("Sheet1").Rows.Count).End(xlUp))


Report •

#22
October 27, 2016 at 01:12:45
Without seeing your workbook I cannot go any further. I have implement the code as I have understood your requirements so cannot do much more than that right now.

in response to your question in reference to the text in bold

The code will look through all names in Column O, from O5 to end
If it finds a match it will take the value in column N and but into column B

Bcell.Offset(0, 1) = CCell.Offset(0, -1)

it will also take column C and put into column C

Bcell.Offset(0, 1) = CCell.Offset(0, -12)

Column O = 15
Column N = O-1 (N)
Column C = O-12 (C)

To answer your question about whether you can refer to an open workbook, yes, this is possible

The error you are receiving is probably because your worksheet isn't called 'Sheet1'

I can ONLY code from the details I have, this code is generic and needs to be modified to suite your needs/environment.

As I said without seeing your workbook or without the details I an unable to code it exactly.

message edited by AlwaysWillingToLearn


Report •

#23
November 4, 2016 at 07:12:25
Here is the final code which worked for Kaygee

Sheet code

Dim WB As Workbook
'WB is a variable of type Workbook. This will be used to reference an open workbook

Dim WS
'WS is avriable of type variant this will be used to reference a worksheet within a workbook(WB)

Sub LookUpStuff()
' This sub has been named LookUpStuff but can  be named anything as long as the word(s) used are not reserved words and contain no illegal charectors

Application.ScreenUpdating = False
'This line of code helps speed up VBA code by refraining from updating a worksheet as the codes progresses in realtime.
'Once the code completes its task outputs are presented on the worksheet.
'It is important that you set this to true once your code finishes otherwise there may be issues with the worksheet not updating.

    Dim Bcell, CCell As Range ' Bcell and Ccell as of type Range, a range or cell will be assigned to them
    Dim TempString As String ' Tempstring will hold thename of the master workbook being queried.
    
    TempString = GetWBName
    'Tempstring is assigned the returned string from Function GetWBName.
    
    'This is evaluatied here to ensure that the returned value is not empty.
    
    If TempString = Empty Then
        ' If TempString is empty them present an error message and stop the code from running any further
        MsgBox "No file wa selected"
        End
    Else
        'if TempSrtring isnot empty then set the variable WB as the TempString
        'Set WS as the worksheet within WB
        Set WB = Workbooks(TempString)
        WS = ("PR Current Sheet")
    End If
    
    'Here we loop through all the cells in the quering workbook from A1 to the last used cell in A
    For Each Bcell In Range("A1", Range("A" & Rows.Count).End(xlUp))
                 
            'Now we loop through all the cells in Column O of the master workbook (WB) to find a matching name
            For Each CCell In WB.Worksheets(WS).Range("O5", WB.Worksheets(WS).Range("O" & WB.Worksheets(WS).Rows.Count).End(xlUp))
                
                'to prevent issues with different cases in name, we dynaically change the case of both the name being
                'looked up and the name in column O to lowercase, this doesnt happen on the worksheet, it is only done within the code
                'so the original data on the sheets is not affeced.
                
                'If the lookup value matches the value in WB column O
                If LCase(CCell) = LCase(Bcell) Then
                    
                    'Then we can use the Offset function to move either left or right from that cell (CCell) in column O
                    'a negative number will move to the left and a positive will move to the right.
                    'in this case minus 1 from column O will move to Column N
                    'a minus 12 will move to column C
                    
                    Bcell.Offset(0, 1) = CCell.Offset(0, -1)
                    Bcell.Offset(0, 2) = CCell.Offset(0, -12)
                 
                End If
            Next CCell
    Next Bcell

'it is paramount to set screenupdating back to true once the code has complted
Application.ScreenUpdating = True

End Sub

Private Function GetWBName() As String
    
    ' This is a returning function, which will call up a userform, and assign a value to the variable WBSelected
    FrmWBSelector.Show
    GetWBName = WBSelected
   
End Function

Private Sub CommandButton21_Click()
    'This is the code which triggers the process from within the sheet using the command button. This button called the LookUpStuff routine.
    LookUpStuff
End Sub

Form Code

Private Sub UserForm_Activate()
    'When this form is called it will populate the listbox with the names of all open workbooks for you to select.
    For i = 1 To Workbooks.Count
        LstWBS.AddItem Workbooks(i).Name
    Next
End Sub

Private Sub CMDOK_Click()
    
    'If you do not select a value within the workbook, WBSelected is set to empty so that that LookUpStuff is able to present an error
    'this is also done so that if WBSelected had already a value assigned to it, the wrong workbook is not used for the search.
    WBSelected = Empty
    
    If LstWBS.ListIndex >= 0 Then
        WBSelected = LstWBS.List(LstWBS.ListIndex)
    End If
    Unload FrmWBSelector
End Sub

Module Code

Public WBSelected As String
'This public variable is used so that a UserForm is able to assign a value to it, and then this variable is able to be used in any other module.



Report •

Ask Question