Solved

Excel Macros?  Possibility to do this?

Posted on 2016-08-01
11
35 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 80

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 80

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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 80

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 80

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 13

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

757 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