Free Microsoft Excel 2013 Quick Reference

- Concatenate and vlookup function in vba
- Referencing a dynamic range in a VLOOKUP function
- Translating worksheet functions into VBA (vlookup, indirect, concatenate)
- Adding " to a VLOOKUP in vba
- Mapping to alter user msg input and create specified name
- Using Worksheet Functions
- Nested IF in VBA HELP!!!!
- 2 way Vlookup - Creating array arguments from columns
- What Excel functions changed your life/career?
- VBA Function error and display result in sheet
- Using indirect worksheet functions and vlookups
- 2 way Vlookup - Creating array arguments from columns
- 2 way Vlookup - Creating array arguments as needed
- Loop through autofilter and paste to new worksheets
- Which function/s should be used on a multi conditional formula?
- Camera Function or Macro?
- VLOOKUP performance issues
- Multiple lookup function fails when called as a UDF
- List unique substrings
- Change cell value on hyperlink click

I have the following problem:

I need use the following formula in a vba function: (but keep

failing)and then place the result in a text/prn file.

=CONCATENATE('Inputs'!E2,'Inputs'B2,VLOOKUP('Inputs'!E2,EmpInfo!A:D,3,FALSE),"780",'Inputs'!F2,'Inpu ts'!G2))

Eg.1

E2 = 012 (text field, usually has leading zero)

E2 = 05050 ((text field, usually has leading zero)

Vlookup answer = " " (5 spaces)

F2 = 01012005 (date)

G2 = 2377000 (salary of 23770.00 - formatted)

The result would be:

01205050 010120052377000

Eg.2

E2 = 010 (text field, usually has leading zero)

E2 = 12345(text field, usually has leading zero)

Vlookup answer = " " (6 spaces)

F2 = 05112005 (date)

G2 = 5555000 (salary of 555550.00 - formatted)

The result would be:

01012345 0511200555555000

I then need to do this for all the rows of data in the input sheet,

and then export the data as a text/prn file.

Your help would be much appreciated.

Kind Regards

Flick

I've spent the whole day playing around with VBA to automate a project that I have to undertake weekly at work and which usually eats up several hours. I'm very pleased to say that it now necessitates only seconds. Although everything works, there's one element in the process which I couldn't work out as precisely as I would like and so I've found an interim solution to work around it. Being a perfectionist (or, at least, somebody who gets obsessive about detail) I'd like to do it the proper way.

Because referring to the real scenario would involve lots of jargon, several sheets and distractingly convoluted formulas, I've dumbed down the example I'm going to give. Instead of referring to my work project, I'm going to pretend that I'm interested in finding out what animal the Chinese named the year after when a selection of US presidents took office.

I have two sheets in my workbook: Sheet "Presidents" features the year in column A and the name of the president who took office in that year in column B. In column C I will use a VLOOKUP function to match the year in column A of that row to the entries in column A of sheet 2 ("Chinese") and import from the relevant row of column B, which shows what animal that year was named after.

Here's my dilemma: In my real project, the number of rows in both sheets will vary between weeks. I can, without a problem, identify the relevant ranges in my VBA. If I want to apply a formula in column C of sheet "Presidents" for every row in which there is content in column A, then that's no problem. I simply set a range rowsPresidents and use concatenation to change "ActiveSheet.Range("c2:c100).Formula = ... " or "ActiveSheet.Range("c2:c$24530").Formula = ..." to ActiveSheet.Range("c2:c$" & rowsPresidents).Formula = ... "

The problem is when identifying the range in which to look for matches in the VLOOKUP function I can't work out how to factor in rowsChinese, so have to resort to entering a defined range such as "a2:b400" or "a2:b45120" instead of some version of ("a2:b" & rowsChinese) as I would wish to.

Here's my example:

VB:I would welcome people's advice and suggestions on what to do. In my real project the range to search in for the VLOOKUP function can be 9000 rows long in some weeks and 900 in another. The same is true for all the content held on the various other sheets in that workbook, since their content is imported weekly. Ideally I'd like to be able to define at which row the range should end, instead of thinking to myself "Insert a really large number just to be sure that no content is missed out."ChineseYearsWhenPresidentsTookOffice() 'The number of rows that the lists of presidents and Chinese years occupy on their sheets vary over time 'so we need to set a range in each particular instance. 'There are headers in sheet "Presidents" so I start my range at A2 rather than A1. There are no headers in sheet "Chinese" so the range starts at A1. rowsPresidents = Sheets("Presidents").Range("a2").CurrentRegion.Rows.Count rowsChineseYears = Sheets("Chinese").Range("a1").CurrentRegion.Rows.Count Sheets("Presidents").Select 'If I wanted to change the contents of column A on sheet "Presidents" to display in italics I could use one of the following 'making use of rowsPresidents via concatenation to apply the command to that particular range, even though the number 'of rows won't necessarily be the same every time. 'With Range("a2:a$" & rowsPresidents) '.Font.Italic = True 'End With 'Range("a2:c$" & rowsPresidents).Select 'Selection.Font.Italic = True 'Here's where I'm stuck: 'Let's go to the list of presidents and apply a formula in column C in every row that a president is featured. 'Fortunately, we've already defined the number of rows and so can insert the final row number in the range via concatenation 'using the variable rowsPresidents. So: "ActiveSheet.Range("c2:c$" & rowsPresidents).Formula =" is the start and works fine. 'I would like to do the same in the VLOOKUP function, telling it to look in columns A and B of sheet "Chinese" 'from row 1 to the end of the pre-defined range using rowsChineseYears. ' I can't see how, though, so have to instead enter an arbitrary row number which I hope will cover all the range. ActiveSheet.Range("c2:c$" & rowsPresidents).Formula = _ "=VLOOKUP(a2,Chinese!a$2:b$400,2,FALSE)" End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I've attached an example spreadsheet to help with visualising what I'm talking about.

Thanks in advance,

Tim

Presidents.and.Chinese.Years.xlsm

I have the following formula on sheet1 -

'=INDEX(INDIRECT(VLOOKUP(D9,INDIRECT(CONCATENATE("solver_",D10)),2,FALSE)),1,VLOOKUP(D11,solver_columncounter,2,FALSE))

D9, D10 and D11 are all user selected values (drop downs)

The line works perfectly - based on the selections, returns the value of a specific row/column in the named range.

Can someone help me translate this to VBA so I can have this run on the backend? I will be setting the returned value to a variable and using it in solver. I'm trying to avoid having to reference the cell on the page for the variable value.

Thanks for the help!!

Im trying to do a VLOOKUP and return the value if it is true or 0 if it is false which works fine, but the problem is im trying to put the formula in when a macro is run and its having issues with the CONCATENATE function as I have to add string values to Column A. Well at least thats what I think the problem is:

VB:ok This returns the value in the formula with:ActiveSheet.UsedRange.Rows.Count - 4 LeaversYTD = "=IF(ISERROR(VLOOKUP(CONCATENATE(A" & i & "," & " " & " " & "," & "Total" & "),'[Leavers_Report.xls]YTD Summary LOS'!$B:$D,3,0))=TRUE,0,VLOOKUP(CONCATENATE(A" & i & "," & " " & "," & "Total" & "),'[Leavers_Report.xls]YTD Summary LOS'!$B:$D,3,0))""" Next i Cells(i,2).Value= LeaversYTDIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

CONCATENATE(A2, ,Total) when it should read CONCATENATE(A2," ","Total")

so I suppose my question is how do I add " into the formula.

Any help would be appreciated.

I'm new to non-record VBA, good with Excel. Any help would be appreciated.

The description of the requirement;

The user will be selecting a cell they want named, I intend to set up a button to activate this procedure and give the cell a different name (as opposed to the user naming the cell via the drop down menus, as the name they are trying to apply contains numeric characters, which Excel does not appear to accept in specified names)

So, the button will activate a input box, the user will enter the name they need e.g "R00551".

Problem 1

I wish to map the numeric characters to alpha characters, without the user seeing, so that the name can be created and applied to that cell.

e.g Mapping 1=A, 2=B etc... up to 0=J.

So "R00551" input results in that cell being named "RJJEEA".

The reason I am doing this, is that I need a vlookup function on another sheet to reference this cell's data, and I have no scope for altering the spreadsheets contents or immediate cells around the cell I need the data from. The users will only know the numeric code to name the cell, of which there are thousands, so no pre input-mapping.

Problem 2

Also I wish to include a drop down selection after the first input box, for a further input, allowing selection from a predefined list e.g Var, Profit etc..

This would then be concatenated to the first re-mapped input to also be included in the cell name.

So the user inputs "R00551", and is then asked e.g "Which category" and is given the five category choices of which they select from e.g "Profit", the result would be "RJJEEAProfit" and that would be the name that I need applied to the cell.

Problem 3

I then need that created name to be applied to that cell as a name.

And then the code is finished and back to the user.

Hope that's enough info.

Thanks

I am using a VBA form to insert a new row containing data into a worksheet. The first step of the code inserts a concatenation of the information into column A, then performs (or attempts to perform) a few Vlookup and MID functions based on the concatenation. The functions work just fine if I do them directly in the worksheet, but not if I try to do them through VBA.

If somebody could please cast an eye over the code and let me know where I am going wrong it would be greatly appreciated. As you can see, there are two different syntax's for the VLOOKUPS, neither of which work.

VB:What I want to do is have the functions performed by VBA, leaving only the value in the cell.Private Sub Submit_Click() Dim NewRow As Long Range("A7:A8").Select Selection.AutoFilter Selection.AutoFilter Field:=1 NewRow = ActiveSheet.Range("A812").End(xlUp).Row + 1 With ActiveSheet Range("A" & NewRow) = ComboBox1.Value & ComboBox2.Value & ComboBox3.Value & ComboBox5.Value & ComboBox4.Value UserForm1.Hide Range("B" & NewRow).FormulaR1C1 = "=Mid(RC[-1],10,5)" Range("C" & NewRow) = Application.WorksheetFunction.VLookup(Range("A" & NewRow), Sheets("Lookup").Range("N:Q"), 2, False) Range("D" & NewRow).FormulaR1C1 = "=Mid(RC[-3],7,3)" Range("E" & NewRow).FormulaR1C1 = "=Vlookup(R[0]C[-4],Lookup!N:Q,3,False" Range("F" & NewRow).FormulaR1C1 = "=Mid(RC[-5],15,25)" End With ' Range("A7:A999").Select ' Selection.AutoFilter ' Selection.AutoFilter Field:=1, Criteria1:=Left(Range("A3"), 3) & "*", Operator:=xlAnd MsgBox "Please Re-Filter Rows", vbDefaultButton1, "SGA Template" End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Cheers!

Paul

Can someone help me with the VBA code?

Here is an example of the first 7 nested IFS

(1) IF(MID(A7,8,9)="GIR",INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0)),

(2) IF(MID(A7,8,9)="NL",INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0)),

(3) IF(MID(A7,8,9)="BE",INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0)),

(4) IF(MID(A7,8,9)="AT",INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

(5) IF(MID(A7,8,9)="DE",INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

(6) IF(MID(A7,8,9)="PH",INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

(7) IF(MID(A7,8,9)="AUBE",INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

I tried this code but it is not working.

Function LookupRetrieve(pVal As String) As Long

If pVal = "(MID(A7,8,9)="GIR" Then

LookupRetrieve = INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!

C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

ElseIf pVal = "(MID(A7,8,9)="NL" Then

LookupRetrieve = INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!

C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

ElseIf pVal = "(MID(A7,8,9)="BE" Then

LookupRetrieve = INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!

C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

ElseIf pVal = "(MID(A7,8,9)="AT" Then

LookupRetrieve = INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!

C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

ElseIf pVal = "(MID(A7,8,9)="DE" Then

LookupRetrieve = INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!

C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

ElseIf pVal = "(MID(A7,8,9)="PH" Then

LookupRetrieve = INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!

C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

ElseIf pVal = "(MID(A7,8,9)="AU" Then

LookupRetrieve = INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!

C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

Else

LookupRetrieve = "isingcode missing"

End If

End Function

The formula is a vlookup formula (Index/Match but with multiple criterias such as the use of Mid).

I tried Concatenate but the formula is too long and has a lot of characters and VBA seems better.

Does anyone have an idea please?

PCBD error on submitting the post)

Hi,

I have base data in column A, B, C and D extending from row 2 to row

500. The data in coln A and B together constitute an unique ID.

I also have data in Column G and H and these 2 together correspond to

the I formed from Column A and B. I want a formula which can return the

value from Column D for a particulr ID combination in Column G plus H.

Ideally I would concatenate the data in A and B ( = A2&B2) and use this

ID for lookup with the concatenated data from G and H (=g2&h2). BUT, in

this case due to the naure of the data layout (and some other factors)

I cannot afford to create new columns for concatenation.

Hence, I want to know as to how I can perform a 2 way lookup without

doing any VBA coding.

I thought that if I can create an array "on the fly" and pass as

arguments which could form a table_array then my problem could be

solved. Something like

(probably this needs to be entered as an array formula)

J2 = Vlookup(G2&H2, union((A2:A500)&(B2:B500),(C2500)),3,false)

I know that the above formula is invalid and Union function exists only

in VBA, but I wrote this to just explain as to what I want to do.

Please guide me.

Regards,

Hari

India

PS : I know some amount of VBA and can do the above using it, but I

just want to know if excel function magic can solve this.

I have been an observer of this forum but recently joined (because I needed some help!).

And, I was just able to offer my first piece of advice back to the community. Woo Hoo!

The evidence

I have a long way to go in my Excel journey I was just wondering what functions changed people's life in terms of career and outlook on how to use Excel.

IF

Where would I be without If?

Such a great function that allows multiple options in one cell.

Changes the way I processed information

VLOOKUP

I was transferring data manually in one job and my manager at the time and asked how I was doing.

I was saying it was going finr. Although, I was kind of new to the job and was struggling to see how I could do this all in a data especially without mistakes.

He came round and asked how I was doing it. He asked if I knew what the VLOOKUP function was.

I said no. He sat down with me and I was literally shocked, surprised and amazed.

When I show other people who have a similar problem the look of wonder that appears on their face is child-like!

Deffo one of the best functions I think that shows people why Excel can be so powerful.

SUBSTITUTE/TRIM/CONCATENATE/LEFT/RIGHT

I am almost always using the above functions to cleanse my datasets

Often getting them ready to be vlookupped!

INDIRECT

This is such a neat little feature. I must have gone a solid year without ever having come across it.

Sometimes you may have ten different ranges e.g. Admissions to different hospitals over a year separated by months.

You have a formula you want to run and then you want to give the user option to see the different values dependent on what they choose in filter.

But, your formulas are dependent on a range. How do you change the range dyamically?

I'm doing a rubbish job at explaining it but follow this video here http://www.youtube.com/watch?v=L6Ow1wLfw2Q

Anyway, just thought I would ask: What Excel functions changed your life?

I am particularly interested what got you to use VBA as I have tried to learn it but I usually give up and decide let me go with a formula instead.

either 9, 10 or eleven spaces) will not concatenate to the other

strings.

Function FuncSpaces(PayGp)

Dim x As String

Dim NoOfSpace As Variant

Set NoOfSpace = Sheets("EmpInfo").Range("Spaces")

Code = 780

If IsEmpty(ActiveCell) Then

Exit Function

End If

x = Application.VLookup(PayGp, NoOfSpace, 2, False)

FuncSpaces = x

End Function

Sub CreatePrn()

Dim EmpNo, PayGp, Code, Prn, EffDate, Sal As String

Range("A2").Select

EmpNo = ActiveCell.Offset(0, 1)

PayGp = ActiveCell.Offset(0, 4)

Code = 780

EffDate = ActiveCell.Offset(0, 5)

Sal = ActiveCell.Offset(0, 6)

Do

Call FuncSpaces(PayGp)

Prn = (PayGp) & (EmpNo) & (NoOfSpace) & (Code) & (EffDate) & (Sal)

ActiveCell.Offset(1, 0).Select

Loop Until IsEmpty(ActiveCell.Offset(0, 1))

End Sub

I have watched all the variables and when the function runs it

displays in watch window as " " but when the sub concatenates

all the variables the spaces arent included.

Also, how do I place each instance of the concatenation in a different

sheet.

ie i want the final result of eg 00512345 7800101200415500 to be

placed into new worksheet, so I can then export as text file.

Your help is much appreciated.

Flick. x

Sub Update_Formulas_Data() Dim sPath1 As String Dim wb1 As Workbook Const sFileInp1 As String = "Formulas.xlsm" sPath1 = fPath & fDate & "_157Support_Summaries" Set wb1 = Workbooks.Open(sPath1 & sFileInp1) Range("AV1").Value = fPriorDate Range("AV1").NumberFormat = "dd-mmm-yyyy" End SubIt works. I need help creating a vlookup formula which will use that cell refernce. First I have to concatenate the cell reference with the suffix "_CDS", and I must do so while mainting the dating format, dd-mmm-yyyy. Thats is where I am currently stuck. After that is done, I believe I should be using the Indirect worksheet function, but I am not sure how that is done. Any help is greatly appreciated.

PCBD error on submitting the post)

Hi,

I have base data in column A, B, C and D extending from row 2 to row

500. The data in coln A and B together constitute an unique ID.

I also have data in Column G and H and these 2 together correspond to

the I formed from Column A and B. I want a formula which can return the

value from Column D for a particulr ID combination in Column G plus H.

Ideally I would concatenate the data in A and B ( = A2&B2) and use this

ID for lookup with the concatenated data from G and H (=g2&h2). BUT, in

this case due to the naure of the data layout (and some other factors)

I cannot afford to create new columns for concatenation.

Hence, I want to know as to how I can perform a 2 way lookup without

doing any VBA coding.

I thought that if I can create an array "on the fly" and pass as

arguments which could form a table_array then my problem could be

solved. Something like

(probably this needs to be entered as an array formula)

J2 = Vlookup(G2&H2, union((A2:A500)&(B2:B500),(C2:D500)),3,false)

I know that the above formula is invalid and Union function exists only

in VBA, but I wrote this to just explain as to what I want to do.

Please guide me.

Regards,

Hari

India

PS : I know some amount of VBA and can do the above using it, but I

just want to know if excel function magic can solve this.

I have base data in column A, B, C and D extending from row 2 to row

500. The data in coln A and B together constitute an unique ID.

I also have data in Column G and H and these 2 together correspond to

the I formed from Column A and B. I want a formula which can return the

value from Column D for a particulr ID combination in Column G plus H.

Ideally I would concatenate the data in A and B ( = A2&B2) and use this

ID for lookup with the concatenated data from G and H (=g2&h2). BUT, in

this case due to the naure of the data layout (and some other factors)

I cannot afford to create new columns for concatenation.

Hence, I want to know as to how I can perform a 2 way lookup without

doing any VBA coding.

I thought that if I can create an array "on the fly" and pass as

arguments which could form a table_array then my problem could be

solved. Something like

(probably this needs to be entered as an array formula)

J2 = Vlookup(G2&H2, union((A2:A500)&(B2:B500),(C2:D500)),3,false)

I know that the above formula is invalid and Union function exists only

in VBA, but I wrote this to just explain as to what I want to do.

Please guide me.

Regards,

Hari

India

PS : I know some amount of VBA and can do the above using it, but I

just want to know if excel function magic can solve this.

I am a newbie to writing vba, so far only really updating/changing code that I’ve recorded using the record macro function. However I’m trying to achieve a result which is beyond my current skills. I’ve tried to describe below as best I can what I’m trying to achieve, any help greatly appreciated, and hopefully I can learn a few things along the way….

Each month I receive a payroll file containing every employee’s pay data (example file attached). Every employee has a cost centre number, e.g. 600 – finance, 610, marketing etc. The cost centre managers have to check the data for the employees they manage, so I need to send them a summary sheet. So I need to . 1) create a new worksheet within the file for each cost centre containing the pay details of the employees within that cost centre, and 2) create a new workbook (saved in a newly created folder) from each of the worksheets so that I can email them to the managers.

I've listed below what it is I'm trying to get the macro to do - hopefully this gives everyone a clear idea of the result I’m trying to achieve…

Stage 1: Create new worksheets within source file:

Apply autofilter to column titles in Row 1 of worksheet Pay_DataLoop through the autofilter on column c (cost centre) for each of the cost centres in named range “cost_centres”, copy/pasting the autofilter result into a new worksheetThe copied data to always be pasted into cell A8 of each new worksheetCopied data to be PasteValuesEach worksheet to be named per the cost centre number being filtered onCell A2 of every worksheet to contain the title “Payroll Data”Cell A4 of every worksheet to contain the cost centre number of the pasted dataCell A6 of every worksheet to contain the cost centre managers name by using vlookup of range “cost_centres” and returning column 3.In each created worksheet, insert a total row at the bottom of each set of pasted data to total each column.Hide columns that total zeroSave workbook (this will then be used as a master file to reference back to)

Stage 2: Create new workbook from each new worksheet:

A new folder to be created within the same file path as the originating workbook called “Pay Extracts”Copy each new worksheet (so excluding worksheets Pay_Data and Cost_Centres) to a new workbookEach new workbook to be saved (and closed) in the newly created “Pay Extracts” folderFilename for each workbook to be a concatenation of columns 1, 2 and 3 within the named range “cost_centres”, e.g in my example file “c:my documentsSep Pay FilesPay Extracts600 Cost Centre 600 Manager 33.xlsx”Each file to be .xlsx

Payroll example 2.xlsx

Many thanks

Alan

The data needs to be separated into 12 catagories (groups) and ranked within

each group based on one, or more, of the values of the set.

I have tried various combinations and presently am using 12 rows, each

testing for one of the 12 catagories (groups) and then concatenating those

results to display a unique text display for each. I will then sort on them

to arrange the groups.

For the ranking I plan on using another series of rows testing for the

specific value, assigning an associated value and then sorting them.

They say a picture is worth a 1000 words and in my instance, probably 5 or 6

thousand. So:

A B C D

1 798 623 111 +IF(AND(A1=500,C1A1),"Group1","")

2 121 697 001 Copy of D1

3 065 000 982 Copy of D1

4 721 348 216 Copy of D1

Cells E, F, --> contain the additional conditional tests and concatenations

to comlete the task.

I have NOT managed to complete this task, hence the purpose of this post.

IS there an easier way to do this???

I have briefly looked at VLOOKUP, Pivotal Tables, and even VBA. Rather than

trying to learn all of them, I was hoping for a suggestion from this fourm

that will point me in the right direction.

Thank you for any consideration you give this request,

Best regards,

Chuck

2.Have a store listing of 500 units

3.I am printing call record forms for use by reps that make calls on the

units.

4.The actual worksheets fill in info on this call record form, triggered by

a store number (lookups data from#2 above, e.g. address, phone#, etc), this

becomes a single page, with space for comments etc, without having to write

in all the contact data. Simply enter the store # and the form is printed

out and ready for use.

5. This call record form is 12 columns wide to contain all the info and to

fit on 8.5x11 sheet of paper.

The question: The new county data I want to include on this sheet is 15-20

columns of data; which is too wide to fit on the call record. Is it possible

to use the camera function to "snap" say columns 25-45 and paste it to the

call sheet to fit in a specified area? (This data would be static and used

only for reference, no math performed.)

Can VBA do this? Obviously, I want to automate this, so that when a new

call record form is printed, this new county data would be on the sheet.

This would be similar to using Vlookup and Concatenate to pull the coulmns

of data...I'm looking for an easier way. Could these Coulmns be Named, and

the use Vlookup to pull the named range as "one piece" so as to fit in the

space allowed?

Appreciate any thoughts.

Here is what the spreadsheet is designed to do:

We have data that is pasted into excel from another source. The data is broken up into account number, date, check number, and amount. Multiple payments from the same account can occur in one month so the data must be totaled. There is a custom function called concatif which works just like sum if but concatenates text. There are VLOOKUPs performed on the account number and the corresponding date, check number, and amount are inserted in the table.

Thanks in advance for your help.

I realized the vlookups were not the problem it was the VBA code.

Here is the concatif funtion

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _ Optional Delimiter As String, Optional NoDuplicates As Boolean) As String ' code base by Mike Rickson, MrExcel MVP ' used as exactly like COUNTIF() with two additional parameters ' of delimiter and "no duplicates" as TRUE/FALSE if concatenated values ' might include duplicates ex. =ConcatIf($A$1:$A$10,C1,$B$1:$B$10,", ",True) Dim i As Long, j As Long With compareRange.Parent Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1"))) End With If compareRange Is Nothing Then Exit Function If stringsRange Is Nothing Then Set stringsRange = compareRange Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _ stringsRange.Column - compareRange.Column) For i = 1 To compareRange.Rows.Count For j = 1 To compareRange.Columns.Count If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j)) End If End If Next j Next i ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1) End Function

I have a fairly straightforward function to concatenate the results from (effectively) a VLOOKUP on a range where there are multiple matches ...

Function MultiVLookup(vMatchCriteria As Variant, rngLookUpArea As Range, lOffset As Long, Optional sDelimiter = ",") As String Dim rngMatchValue As Range Dim sFirstAddress As String Dim sTmpReturn As String sTmpReturn = "" With rngLookUpArea Set rngMatchValue = .Find(vMatchCriteria, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If Not rngMatchValue Is Nothing Then sFirstAddress = rngMatchValue.Address Do sTmpReturn = sTmpReturn & rngMatchValue.Offset(0, lOffset).Value & sDelimiter Set rngMatchValue = .FindNext(rngMatchValue) Loop Until rngMatchValue.Address = sFirstAddress End If End With If Len(sTmpReturn) > 0 And Len(sDelimiter) > 0 Then sTmpReturn = Left(sTmpReturn, Len(sTmpReturn) - Len(sDelimiter)) End If MultiVLookup = sTmpReturn End FunctionIf I call this from a VBA subroutine it works fine, and produces the expected results:

Sub TestCall() MsgBox MultiVLookup("A", ThisWorkbook.Sheets(1).Range("A1:A3"), 1) End SubIf, however, I call it as a UDF it fails. A quick bit of debugging shows that this is because the .FindNext method returns an empty range, which shouldn't be possible, as there are both more matches within the range and, even if there weren't, there must have been at least one match, so it should loop back to that.

Things I've tried:

1. Naming the range explicitly, rather than using a With statement - no difference.

2. Defining another range within the function and setting it to the search range before the first search - no difference.

However, the brainwave I had half-way through typing this post was to replace the .FindNext statement with:

And that does work as a UDF.

So, I guess my question not isn't so much "How do I make this work" as it is "Why the hecky-thump didn't it work with .FindNext". Anybody care to enlighten me?

Thanks.

I have several dynamic named ranges (DNR) that I copy from a datasheet

to a calculation sheet. I am attempting to build a matrix where the

row-headings are the first three characters of the first DNR and the

column headings are the last two characters of the first DNR. The

matrix would then populate using concatenate & vlookup.

Right now it is working using worksheet functions for the mid, concat &

vlookup. However, the number of observations I have is changing enough

that I would like to make it dynamic in VBA.

Thanks for your help.

James

This site has proven to be an invaluable resource, thankyou to everyone who posts both questions and responses. This is the first time I have posted a question although I have had a bit of a look I cant guarantee it hasnt been asked before, if so please point me in the right direction.

I have developed a basic front end for a simple database using the vlookup and index search functions.

Within the front end I have an if function that shows a hyperlink if a search cell is used and a result is obtained

=IF(ISTEXT(Properties!X2), HYPERLINK(CONCATENATE("#", Properties!X2,"hazard"), Properties!Z2), ""))

I also have a combobox that you can select from.

1. Is it possible to have the combobox linked-cell changed to a particular cell (eg. x2) value when someone clicks on the hyperlink cell (only if the hyperlink is active). 2. Does it require VBA or can it be managed using a function?

3. will it update the combobox?

Thanks in advance

Justin