Solved

Excel Macros?  Possibility to do this?

Posted on 2016-08-01
11
40 Views
Last Modified: 2016-09-03
Hi!
Attached is a document that a few people here have helped me with!  I need a bit more help.

I would like to auto populate columns B and C of  worksheet16-Q3 by finding out this information:
When I type a name in column A of 16-Q3 I would like it to look at ALL worksheets for that name and if there is a 4 in that row in column J, I would like it to fill out  Columns B and C of 16-Q3, with what is in  in columns G and H of that same row. B=G and C=H.
I have no idea if this can be accomplished, lol, but I thank you all in advance!
Lisa :)
mastercalltoEE.xlsm
0
Comment
Question by:lisafamf
11 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 41737968
You can do what you want with a user-defined function and a worksheet formula like:
=vlookup3dtest($A19,'Aug ''16'!$A$1:$J$1000,7,FALSE,"Apr '16",10,4) & ""
In the second parameter, note that there are two single quotes preceding 16--not a double quote.

Install the user-defined function in a regular module, just like it was a macro.

As written, the UDF returns #N/A (as text) if it cannot find the test value (4 in your request) in the lookup sheets. Since your test case had one column that was blank and you  were returning text, I appended an empty string to the value returned by Vlookup3DTest.
Function Vlookup3DTest(LookupValue As Variant, LookupTable As Range, ReturnColumn As Long, DataSorted As Boolean, LastSheetName As String, _
    TestColumn As Long, TestValue As Variant) As Variant
Dim ws As Worksheet
Dim wb As Workbook
Dim rg As Range
Dim i As Long, FirstSheetIndex As Long, LastSheetIndex As Long
Dim addr As String
Dim v As Variant
Application.Volatile
Vlookup3DTest = "#N/A"
addr = LookupTable.Address
Set wb = LookupTable.Worksheet.Parent
Set ws = wb.Worksheets(LastSheetName)
FirstSheetIndex = LookupTable.Worksheet.Index
LastSheetIndex = ws.Index
For i = FirstSheetIndex To LastSheetIndex
    Set rg = wb.Worksheets(i).Range(addr)
    v = Application.VLookup(LookupValue, rg, TestColumn, DataSorted)
    If Not IsError(v) Then
        If v = TestValue Then
            Vlookup3DTest = Application.VLookup(LookupValue, rg, ReturnColumn, DataSorted)
            Exit Function
        End If
    End If
Next
End Function

Open in new window

mastercalltoEE_Q28960827.xlsm
0
 

Author Comment

by:lisafamf
ID: 41738001
Hi Byundt, but I can't open the file you attached?
0
 
LVL 7

Expert Comment

by:tomfarrar
ID: 41738084
I think you may have a problem when you look at all sheets as Column J is not consistent on all worksheets.  Perhaps I am not understanding what you are looking for, but there is no column J in Jan-Jun.
0
 
LVL 81

Expert Comment

by:byundt
ID: 41738175
lisafamf,
The file I posted has a .xlsm extension and corresponding file format.

When I tried to download the file from Experts-Exchange, however, I see that they added a .xlsx file extension to it. They did the same thing to the file you posted, too.

Please download the file onto your computer without trying to open it. Then rename the file to delete the .xlsx at the end of the file name. Make sure the file name ends with .xlsm and you should then be able to open the file in Excel.

I elevated the issue to Experts-Exchange technical support. After doing so, I learned they pushed a fix for the problem tonight, though it isn't working for me. FWIW, Chrome seems to download the file just fine. Both Firefox (my primary browser) and Internet Explorer have problems downloading .xlsb and .xlsm files on Experts-Exchange.

Brad
0
 

Author Comment

by:lisafamf
ID: 41739723
Hi Tom and Byundt, thanks for your responses!  I won't be able to address them until Wednesday, as I was slammed with something else.  Thanks!!!
Lisa
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:lisafamf
ID: 41741217
Hi byundt,
I was able to open the file!
My issues is, I'm a total novice and don't really understand anything your wrote above, lol.
So, are  you saying I can get the results I want?
It does work when I put that one particular name in, but there is another name with a 4 in column J, and that is not populating the information?
I am a total novice!!!
Thanks,
Lisa
0
 
LVL 81

Expert Comment

by:byundt
ID: 41741225
Lisa,
The posted code and workbook are supposed to solve the problem you posted. And they did so in my tests.

If it doesn't work at your end, please post the workbook that reproduces the problem.

Brad
0
 

Author Comment

by:lisafamf
ID: 41741240
It only worked with that one girls name.  Here is the file attached.  Thanks!
I trust what you did, it's me I don't trust ;)
Thanks, Brad.
mastercalltoEE_Q28960827.xlsm
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 41741277
Lisa,
It appears that there may be multiple instances of a name on any given worksheet. I assume that you wish to check each instance for a 4. If found, then return values from columns G and H. Otherwise, go on to the next worksheet.

This new understanding required me to rewrite my code,
Function Vlookup3DTest(LookupValue As Variant, LookupTable As Range, ReturnColumn As Long, DataSorted As Boolean, LastSheetName As String, _
    TestColumn As Long, TestValue As Variant) As Variant
Dim ws As Worksheet
Dim wb As Workbook
Dim rg As Range, cel As Range
Dim i As Long, FirstSheetIndex As Long, LastSheetIndex As Long
Dim addr As String, addrFirst As String
Dim v As Variant
Application.Volatile
Vlookup3DTest = "#N/A"
addr = LookupTable.Address
Set wb = LookupTable.Worksheet.Parent
Set ws = wb.Worksheets(LastSheetName)
FirstSheetIndex = LookupTable.Worksheet.Index
LastSheetIndex = ws.Index
For i = FirstSheetIndex To LastSheetIndex
    Set rg = wb.Worksheets(i).Range(addr)
    Set cel = Nothing
    On Error Resume Next
    Set cel = rg.Columns(1).Find(LookupValue, LookAt:=xlWhole, LookIn:=xlValues, MatchCase:=False)
    If Not cel Is Nothing Then addrFirst = cel.Address
    On Error GoTo 0
    If Not cel Is Nothing Then
        Do
            v = cel.Cells(1, TestColumn).Value
            If v = TestValue Then
                Vlookup3DTest = cel.Cells(1, ReturnColumn).Value
                Exit Function
            End If
            Set cel = rg.Find(LookupValue, After:=cel, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
            If cel.Address = addrFirst Then Exit Do
        Loop
    End If
Next
End Function

Open in new window

I attached a copy of your workbook with the revised code. Remember to change the file extension to .xlsm if necessary before opening the file.]

Brad
mastercalltoEE_Q28960827-1-.xlsm
0
 

Author Comment

by:lisafamf
ID: 41741280
yes, that is what I want, thank you thank you thank you.  I'll open it tomorrow, gotta run.  I sure appreciate  your help :)
Lisa
0
 
LVL 14

Expert Comment

by:frankhelk
ID: 41782751
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Accept: byundt (https:#a41741277)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

frankhelk
Experts-Exchange Cleanup Volunteer
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now