Doing IF Compare Against Concatenated Value In VBA
Jan 31, 2013
struggling with the syntax of this one. Lets say we have a variable called MYWEEK with a value of WEEK 9 and cells A1 and A2 have values of 9 and 10 respectively. I need to be able to compare the variable against cells A1 or A2 but with a prefix of WEEK .
I've tried the following:
if MYWEEK = "WEEK " & A1 then ...
if MYWEEK = "WEEK " + A1 then ...
if MYWEEK = ("WEEK " & A1) then ...
I know the answer is staring me in the face but I cant see it. Any use of the ampersand presumably invalidates the IF statement by introducing multiple conditions rather than a concatenated value. How do you compare a value against a concatenated set of criteria in VBA?
I have a TextBox that I want to show the concatenated value of two cells. Is that possible? Or do I need to concatenate it on the sheet and then have the TextBox = that cell?
Effectively I need to concatenate two columns in Sheet1, and then vlookup each concatenated value from Sheet1 in another concatenated column in Sheet2 and return the value from the next column
Now that is quite straight forward, but I am new to coding, and I was wondering if there was a way of doing it that doesn't involve having the concatenated values written to a column as a formula. Ideally what I would like it to do is put the concatenated value of Sheet1 together as a variable (I think thats what you call them) and then look up that variable in Sheet2 without Sheet2 creating a concatenated column too.
I'm of the understanding that this might be slower than writing the columns, but there is a reason for my madness.
Let me know what you think, or if i'm barking. Any suggested reading would also be handy, I don't like to bug you guys for the full code straight out.
I have a cell that containes a concatenate statement for two named formula. The value taht the cell returns is a multiple of 10 (i. e10, 100, 1000, 10000 etc etc.) then in the adjacent cell, i have a nested if statement giviing differing text dependent upon the other cells value, i.e if less than 1000, return text string of "good" , however the formula does not seem to accept the value given in the concatenate cell.
I'm trying to use this function which was posted as an answer (looking up a single value and returning multiple results, concatenating those results in one cell):
Function getfiles(DRng As Range, LURng As Range) For Each ce In LURng If ce.Value = DRng Then holder = holder & ce.Offset(0, 1).Value & ", " End If Next ce getfiles = Left(holder, Len(holder) - 2) End Function
What is "ce" here? Auto Merged Post;additional info:
the original question was posted by jwhite68, Feb 27th 2007, "Return Multiple Values From Lookup To Single Cell"
I have charts that I have to update at work, and I want to make it as fast of a process as possible. Here is an example of what I want to do.
I want to put the letter B in cell 1, then the number 23 in cell 2. I then want to concatenate those two strings, which I can do. I end up with cell 3 which says B23. I then want cell 4 to to be the value of cell B23. If the B23 in cell 3 changes to C11, I want cell 4 to be the value of cell C11.
I would use an IF(x,x,x), but I want to to take into account the fact that it could be any cell on the whole sheet which would be one hell of a nested IF statement.
I have an address field that is concatenated as such that the city name is attached to the end of the street name as follows:
4 Example WayOakland, CA 94601
I have over 200 of these with different addresses and cities in my workbook so it's not always the same length. How can I grab the city out of this string? Is there a way to key on the capital letter of the city? Or is there another way?
I am trying to figure out if there is an easy way for me to remove duplicate text when concatenating cells together. For example if I have the following cells defined as such
A1 = red green orange B1 = orange blue purple
when i do a CONCATENATE(A1,B1) I obviously get red green orange orange blue purple
How can i get excel to remove that duplication of the word orange without assigning each individual word to it's own cell and using the remove duplicates function?
I need to show sum and not concatenated values have a userform w 60 fields for numbers as follows:
a) UserForm1.TextBox34 to UserForm1.TextBox41: 8 fields-CAD currency b) UserForm1.TextBox42 to UserForm1.TextBox49: 8 fields-USD currency c) UserForm1.TextBox50 to UserForm1.TextBox57: 8 fields-exchange rate d) UserForm1.TextBox58 to UserForm1.TextBox65: 8 fields-Total CAD currency => the formula here works across the board UserForm1.TextBox58.value = Format(UserForm1.TextBox34.value+UserForm1.TextBox42.value*UserForm1.TextBox50.value, "###,##0.00") e) UserForm1.TextBox66 to UserForm1.TextBox73: 8 fields-local taxes in CAD currency f) UserForm1.TextBox74 to UserForm1.TextBox81: 8 fields-federal taxes in CAD currency g) UserForm1.TextBox82 to UserForm1.TextBox89: 8 fields-total for first column in CAD => the formulas here works as well across the board UserForm1.TextBox82.value = Format(UserForm1.TextBox34.value + UserForm1.TextBox42.value * UserForm1.TextBox50.value + UserForm1.TextBox66.value + UserForm1.TextBox74.value, "###,##0.00")
have a grand total formula pulling in all values, and this one works as well; so i have 4 simple addition formulas, and the local & fed tax formulas do not.
however, the formulas for the sub-total for the local taxes and the federal taxes do not work; the results show a concatenation of the 8 values rather thn the sum of the 8 values; the formula for the local tax is as follows: UserForm1.TextBox110.Value = Format(UserForm1.TextBox66.Value + UserForm1.TextBox67.Value + UserForm1.TextBox68.Value + UserForm1.TextBox69.Value + UserForm1.TextBox70.Value + UserForm1.TextBox71.Value + UserForm1.TextBox72.Value + UserForm1.TextBox73.Value, "0.00")
I have rewritten the formulas and still these two tax formulas result in concatenated numbers rather than the sum.
I would have u/l worksheet but it's roughly 4MB now.
I need bolding and underlining cells that are "Concatenated". For example, cell format for cell K12 is already bolded and underlined. If I concatenate (C1,K12,D2) I get a result that doesn't carry over the format of those individual cells - particularly K12 which I want BOLDED and UNDERLINED.
Concatenated cells. My issue is how do i search through a sheet with cell values that have been concatenated?
Lets take an example:
I would like to search for a string call sales in sheet1 which would display all the employees and schedules for that department (do note that employees can be part of not just one department). but my cell formatting is as follows:
Sheet 2, Column 1 Employee name
E.g. Row 2 Employee1
Sheet 2, Column 2 Department (cells concatenated)
E.g. Row 2 Sales, Marketing (Concatenated Cell)
Sheet 2, Column 3 Work schedule
E.g. Row 2 8 AM to 5 PM
So when you search for sales, it will display employee1 and work schedule.
I have a dataset (20,000rows) with Grade, Region and Salary. I need to calculate the 25, 50, 70 and 90 percentiles against each concatenated Grade and Region.
I have a lot of seperate data that I'm trying to graph via a macro. I have one macro with a Do Until/Do While loops that runs before I import the data that creates and seperates the data into dynamic named ranges. My goal was to create a macro that graphs the different ranges using another Do Until/Do While loop. You'll see below that I have the ranges concatenated inside the loop so it does just that, graphs each set of data in each worksheet by running the same macro. Below is the code for the specific graphing macro and at the same spot, where the series values are set using the concatenated range name, the error message, "Unable to set the Values property of the series class." I have no idea what is wrong, but my guess is it's probably something simple.
Sub Graph() Dim Counter As Integer Dim CounterEQ As Integer Dim Title As String Counter = 200 Do Until Worksheets("IndustryEmployment").Cells(1, Head) <> "" Counter = Counter - 1 Loop CounterEQ = Counter Do While Worksheets("IndustryEmployment").Cells(1, Explode) <> "" GoingTo = Worksheets("IndustryEmployment").Cells(1, Explode) Charts.Add ActiveChart.ChartType = xlLine ActiveChart.SetSourceData Source:=Sheets("IndustryEmployment").Range( _ "U15:U16"), PlotBy:=xlColumns.......................
I have a worksheet that is utilizing a VLookup to pull some values from another worksheet based on a composite number. I have checked the numbers for their format, length, and value (dangling spaces, etc.). I still cannot get this VLOOKUP to work correctly. I have attached the file.
Column H on the Cost Report sheet holds the formula in question. It concatenates the values in Columns C,D,E,&F to create a composite number to look for in Column B of the Original Estimate sheet. It is supposed to pull the $ value from Column D of that range.
I have tried everything. I passed the composite number into TEXT, --(Double Unary), INT, tested for both #'s length with LEN and have converted the formatting between text and numbers.
I have a list of skills listed as codes in column A. I would like to write in a separate column, B, the name of the skill based on the fact that A contains a specific text. Only the first condition return a correct value, the others condition returns #VALUE! as if they are not satisfied.
I am taking a decimal degree (longitude) and converting it to binary then to hexadecimal. The value I am using is a Binary Angle 32 bit structure that will be translated into an 4-byte hexadecimal (with two characters per byte i.e. "20" is one byte and results in spaces).
This is what I have:
Input of 359.999999916 (in cell A2) Cells B2 through AG2 I have converted A2 into binary (using formulas like "=IF(A2-180>0,1,0)" for cell B2, "=IF(A2-180*B2-90>0,1,0)" for cell C2, "=IF(A2-180*B2-90*C2-45>0,1,0)" for cell D2, and so on)
I then concatenated all of the results into one cell using the following formula: =CONCATENATE(B2,C2,D2,E2,F2,G2,H2,I2,J2,K2,L2,M2,N2,O2,P2,Q2,R2,S2,T2,U2,V2,W2,X2,Y2,Z2,AA2,AB2,AC2,AD2,AE2,AF2,AG2)
To get a result of (located in cell A4): 11111111111111111111111111111110
However, when I attempted to use the BIN2HEX conversion on A4, I get a "NUM!" error. Here is the formula that I was using in cell A5: =BIN2HEX(A4,8)
I have some data a pivot table I would like them to be concatenated on next column. The X signifies the Blank cell on the pivot table. Now I can concatenate (in 1 cell) using VBA but I don't know how loop thru the Blank cells. I have about 1000 rows of data. I would like Data like this:
Investment Price Source 009451AP0 FTID FTID 009451AP0 Total x 967673Z RWP RWP 967673Z Total x AA.CDS23 MarkIt Markit AA.CDS23 Total x AACE.BD10BA FTID FTID, RWP RWP AACE.BD10BA Total x
I am lookin to apply CF to a cell based on the value of the left two numbers in a concatenated formula. If the value of the left two numbers are greater than zero the apply the CF
I want to create a series of VLOOKUP commands with different lookup tables whose names are concatenated from two different pieces. Here's what I'm trying to do:
LUTWK01, etc., are named ranges. I have the text "WK01", "WK02", "WK03" stored in other places, and would like to be able to concatenate "LUT" and the particular week (e.g., "WK01"). The formula that I've created, which isn't working is:
VLOOKUP(x,CONCATENATE("LUT",A1),y, false) where cell A1=WK01.
When I pull out just the CONCATENATE portion, it resolves to LUTWK01, but apparently this is not recognized as a range name. Any idea as to how I make the VLOOKUP formula recognize a concatenated range name? Or is there a better way to do this?
New user with basic knowledge faced with a multi-part challenge.
I have a list of 10 items (multiple words) in a drop down
Based on what is selected in the drop-down, I am trying to figure out the best way for it to pull the corresponding text paragraph. This would then be concatenated with a numerical value inputed b the user in another cell. The final output would be displayed in a final cell.
I have figured out how to concatenate between one cell and the other cell with the paragraph but I am at a loss for how to make it more dynamic in the sense that the drop down selection drives the paragraph being selected and then taking the value in cell A1 to concatenate within that paragraph and outputting the final text to be copied out from excel.
I tried looking at posts for dependent data verification and I felt like I was on the right track but hit a wall.
I need to return a value from a cell in Column H, but with a row number that varies with each entry.
I repeat this formula every 5 rows or so. But the cells referenced on another spreadsheet are only one row apart. Because of this discrepance, when I copy my highly developed cluster of formulas, they only reference every 5th entry on the other sheet. What I've been doing is manually altering long, complicated formulas by hand. I can't do a replace function because every once in awhile it'll find an extra instance of the digit I'm replacing and mess up parts of the formula I don't want changed. I could avoid this tedious, time-consuming work-around if I could make a formula that would do something like this:
=Display Value from in column H: (row number per cell B1)
And cell B1 would include the number 3789.
So the returned result is the value from cell H:3789
How to grab data from a table providing it fulfills two chained conditions without using a concatenated key. For example:
I have this table and I want to get the combination Name + Color. :
A B C
1 John White
2 John Red
[Code] ........
The database is something like:
A B C D
1 John Red 3254.30
[Code] ......
I ended up using VLOOKUP() and a concatenated key like Name&Color but it slow down the sheet significantly as I have many records and also does not seem the most elegan solution. I tryied using OFFSET() nested with MATCH()but couldn't get it. Also tried something with ARRAY FORMULAS but I am not very proficient at them.
Im trying to come up with an automated way of splitting concatenated information but putting each concatenated value in a seperate row.( i have columns of data that need to stay with each concatenated value) Eg
Concatenated comma delimited data
Cust Name Cust Identifier Locations L Kim 543545 California,Chicago,Florida S David 31434 Maryland,Vermont,Maine D Bryant 572575 Texas,Oaklahoma,Nebraska
i need to to get a result that looks like the below example im currently just doing text to column filterting and copying all the columns over and stacking them on top of each other. i need to find a way to automate this process but i cant seem to think of one
Customer name Customer Identifier Location L Kim 543545 California L Kim 543545 Chicago L Kim 543545 Florida S David 31434 Maryland S David 31434 Vermont S David 31434 Maine D Bryant 572575 Texas D Bryant 572575 Oaklahoma D Bryant 572575 Nebraska
I have a report that is collated from several other workbooks. The source workbooks follow the same naming convention throughout the year and so I have managed to concatenate the file path to bring through the text of the filepath and cell reference however cannot work out how to get the value of the cell. For example, I have the file path in A1, file name in B1 and sheet and cell referernce in C1.
I am using {=(A1&B1&C1)} which gives me R:CONFIDENTIALREPORT FOLDER[REPORT 24.09.07.xls]Sheet1!$D$1. To make matters slightly more difficult, the source reports would be closed. Is there an easy way or am I going to have to do this in VBA.
