For Statements For Multiple Variables With Similar Names

Feb 17, 2013

Basically I have a list that user can select up to 6 values from. I have set them as individual strings.

Public IH1 As String
Public IH2 As String
Public IH3 As String
Public IH4 As String
Public IH5 As String
Public IH6 As String

What I want to be able to do is loop through them and do certain actions. How can I get a for statement to do that.

I tried something like that but it doesnt work. How to get it working I need to do tests on the variable strings and I want to be able to reference them and I'm not sure sure how

For i = 1 To 6
If "IH" & i = vbNullString Then
MsgBox "IH" & i
End If

View 3 Replies


Nested If Statements With Multiple Variables

Apr 29, 2013

I am very new at in depth excel formulas, usually in my daily work I can get by with simple summations, if statements etc. This one has thrown me for a loop.

I'm making a spreadsheet on soil compressibility and I feel like I am just knocking my head against the wall. Here is what I am working with. I need to calculate values in a column, we'll call them "Rm" values.

There are a few basic things that dictate which "Rm" equation you use. I'm substituting V,W,X,Y,Z in for the equations to simplify the question.

I = 3 Rm= W*log(K)

View 9 Replies View Related

Nested IF Statements Or Similar Formula

Oct 16, 2009

Trying to make it even more complicated....probably again misplaced my parenthesis

=IF((AND('Input Page'!B43="-",OR('Input Page'!B13="New Lease In-House",'Input Page'!B13="New Lease w/Co-Broker")),'Analysis Report'!D57*'Input Page'!B32/”2”,'Analysis Report'!D57*'Input Page'!B32/"4",IF(AND('Input Page'!B43="-",OR('Input Page'!B13="Renewal In-House",'Input Page'!B13="Renewal w/Co-Broker",’Input Page’!B13=”Expansion In-House”,Input Page’!B13=”Expansion w/Co-Broker),'Analysis Report'!D57*'Input Page'!B32,'Analysis Report'!D57*'Input Page'!B32/"2"))

View 6 Replies View Related

Several Similar Statements In Different Events In My UserForm Code

Dec 18, 2008

I have several similar statements in different events in my UserForm code. Is it possible to separate these statements in a procedure and call it each it when I need it? The similar statements are:

With Sheets("Knowledge")
For Each rw In .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row)
If ListBox5.List(, 4) = Trim(rw.Value) Then
Label1.Caption = rw.Offset(, 1).Value
Label2.Caption = rw.Offset(, 4).Value
Label3.Caption = rw.Offset(, 5).Value
Exit For
End If
Next rw
End With

If it's not possible to make my code shorter with a procedure, can I optimize somehow this repeated code?

View 9 Replies View Related

Similar Names ....

Feb 21, 2008

As you can see in the example below, in column B I have a list of vendor names, some of which are similar but not identical. (For Example, in one instance a vendor will be called "Ford Motor Co.", while in another it will be called "Ford Motor Inc.".

I need to populate column C, which at every instance where two plants (listed in column A) have similar vendor names in Column B, a universal name will be assigned and recorded in column C for each of the similar names.

HOpefully it is clear as shown below.......

As you can see in the example below, in column B I have a list of vendor names, some of which are similar but not identical. (For Example, in one instance a vendor will be called "Ford Motor Co.", while in another it will be called "Ford Motor Inc.".

I need to populate column C, which at every instance where two plants (listed in column A) have similar vendor names in Column B, a universal name will be assigned and recorded in column C for each of the similar names.

HOpefully it is clear as shown below.......

View 9 Replies View Related

Averaging Numbers From Similar Names

Oct 14, 2009

I have tried looking for similar questions in the forum and couldn't find them. If you know of any, please let me know. My problem is that in one column I have peoples names that are repeated. I need to average numbers that are in the same row as their name. So for example:

NameDateworkPagesRevisions Revisions/PageRH9/16/09Sandy land39320.82Bryce9/18/09Price mixed use1111030.93Bryan F.9/21/09Letter (rates)6325.33Bryan F.9/21/09Bountiful Nursery64871.36Randy9/22/09SJ Subdivision58220.38RH9/25/09Park City office Condos76881.16Bryce9/25/09Evanston Motel62210.34

this list is continually added upon monthly. So for Bryce I would be able to pull 111 and 62 and have an average of 87.5 from the pages column. But if I were to add another row farther down with his name, it would change the average.

I was thinking that a repeated vlookup would work if it stopped when there was a blank in the cell, but I am unsure on how to do this. If you have any questions, let me know. I was hoping to have it in a formula in a cell above the table.

View 9 Replies View Related

Multiple IF Statements (skip The Next Two Statements Or The Result Will Be Changed Again)

Nov 22, 2008

I have three IF statements as below. the problem is if the first statement is true I want it to skip the next two statements or the result will be changed again.

View 2 Replies View Related

Evaluating List Of Similar Names And Assigning Common To Variable?

Sep 17, 2013

I am working on a customer report template that generates our customers reports and will send them out automatically.

This issue I have now is that the system that generates the raw data for these reports only lists the Customers name in a column with an entry for each line of data, the thing is though that the system has lots of variations of the customers name, even more so if that customers has different departments.

What I need to do is from this list of customer names, I need to automatically figure out what the "common" name is or main name so to speak, and then make a variable using the correct full name, which will be used later on in the code to import correct logos, and direct the reports to the correct people.

Here is a quick example of what data we get raw from the system:

Customer Name:
John Build
Johns Buildings
Johns Ltd Building
Johns Plumbing Department
Glass Doors Ltd A Department of Johns Buildings
Johns Building Corporation
Hole In One Golf Range

This is just an example, we have thousands of clients, so the length, number of words etc can change alot. Ideally I from a list similar to that I would get a full proper result of "Johns Buildings Ltd" for example, this would then be in a variable to be used in code from then on to reference doing certain things with the reports of Johns Buildings Ltd.

You'll notice there is one name "Hole In One Golf Range" that seems to have no relation at all, this is correct, ideally I would also like to build in some error checking into the code, so that rows like that that have nothing to do with the others would get deleted.

So how would you amazing VBA gurus go about working with data like this? I'd prefer a more general answer with explainations that just straight code, as I'm sure I will have to adapt the hell out of it for it to be useful in context.

View 9 Replies View Related

Finding Duplicate And Or Similar Info Across Columns And Highlight (names And Addresses)

Jan 29, 2014

Magazine subscription list. How to highlight the customers that are already in the sheet if enter them again (renewal). Our list is like so....

ColA ColB ColC ColD ColE ColF
First Last 123 Ave City State Zip

Is there a way to highlight the row if the info on ColA, ColB, ColE, and ColF all match? Sometimes the Street info is abbreviated or entered PO Box instread of P.O. Box and they wind up on the list a second time.

View 5 Replies View Related

Automatically Select Most Latest File Name From Similar Names Ending With Dates

May 25, 2014

I need to use the VLOOKUP feature from the most latest files, I am using VLOOKUP in VBA code which is working fine, however the problem is that the files from where I do VLOOKUP are added randomly in a week.


There are 2 Files, 1st File where the data would be looked up from the 2nd File, Now the 2nd File has names like this "Offline Data as on 10th May 14", "Offline Data as on 13th May 14", "Offline Data as on 23rd May 14", etc.

Now these files are send by another department, and each time the most recent data has to be searched from these files.. All of these files have to be maintained separately for record purposes.

Hence in the VLOOKUP, Lookup file keeps on is it possible to make a code in such a way that the code picks up the most recent file for reference, from the above example we would need to pull the data from the most latest file which is "Offline Data as on 23rd May 14" , if any other file is added in that folder more recent than the one mentioned than the code should automatically link it to that file.

I have managed to codify the lookup but due to this filenames being changed always I have to do this this has to be done at several columns in several sheets its extremely time-consuming.

So, logic code to pick the most recent date which is embedded in the filename...

View 12 Replies View Related

Assigning Variables To Worksheet Names VBA

Jun 6, 2014

How to assign a "WS(i)" worksheet name and have the assigned variable (i) in the name equal the consecutive steps in a total page count.

Here's the code that does not work:

Dim WB as Workbook
Dim WS as Worksheet

Set WB = ThisWorkbook
For i = 1 To WB.Sheets.Count
Set WS(i) = WB.Sheets(i)

I can see why it does not work. I can't figure out what to declare in my Dim or how to word the statement using the (i) variable in order to capture the value to attach to "WS."

Details: The issue is that the number of pages created will vary day to day depending on staff needs. There's an array of staff names to be assigned to page tabs and there will be skips and deletions along the way depending on daily attendance so "WS(i)" worksheet names can't be assigned at creation. What happens if they are is that the succession of worksheet names winds up being (e.g.) "WS1, WS2, WS5, WS12," etc. instead of consecutive.

There's a very complex data-sort-and-assign system that depends upon how many staff-assigned sheets there are, and as such each sheet has to have a "WS" worksheet name for the process to stay smooth. If the names are not consecutive, though, the sort-and-assign process becomes confused. This step takes place at the end of the initial workbook set up process, immediately before the data are addressed, so there really isn't an earlier opportunity. It can't be later since from this point on the system depends upon the "WS(i)" names being there.

It started fine when there were 5 people. Now there are 37.

View 2 Replies View Related

Switching Between Workbooks Whose Names Are Stored As Variables

Jul 20, 2006

I'm having trouble finding a way to switch between two workbooks that I have open whose names are stored as variables. If the variable name were variable, for example, I have tried the following:

workbook.activate variable
workbook(variable).select variable

none of these work and I've tried a ton of other ways to get it to work but I just can't figure it out!

View 8 Replies View Related

Range Names Created From String Variables

Aug 30, 2006

I work with data that varies in row numbers but is consisten in column width. I am trying to write code that will create a named range for the data but be flexible to expand or contract based on the amount of data that is pulled in. Below is the

Const lngLastPossRow As Long = 65536
Dim strDataRng As String
strDataRng = ActiveSheet.Name & "!R4C1:R" & Range("a" & lngLastPossRow).End(xlUp).Row & "C17"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWorkbook.Names.Add Name:=("Data"), RefersToR1C1:= _

While the result creates a named range called "Data" , it does not allow me to reference and data in any formulas ( sumif's, etc). Can someone tell me what I am missing. It appears to be returning the wrong data type (string as opposed to range values).

View 6 Replies View Related

Can Use Variable Names To Identify / Assign To Other Variables Or Arrays?

Apr 10, 2014

I have set up a number of arrays e.g. DataSetA (1 to 100), DataSetB (1 to 100) etc.

Is it possible to use a string variable (e.g. DataIndentifier) to identify and assign values to certain arrays.

For example, under certain conditions

DataIdentifier would be set to "DataSetA" and under other conditions
DataIdentifier would be set to "DataSetB".

Having done this, I want to assign values such as DataIdentifier(1) = 3.4 or DataIdentifier (10) = 6.2 etc.

This obviously won't work because DataIdentifier is defined as a string not an array. How do I enable the string DataIdentifier to be used as an identifier for the array I want to assign values to? Therefore if DataIdentifier is set to "DataSetA" then effectively I want something like "DataIdentifier(1) = 3.4" to assign 3.4 to the array DataSetA(1).

View 5 Replies View Related

Lookup Multiple Similar Values

Feb 16, 2008

I've search several posts and can't figure this out. Have sheet1 with facility number (A) and Description (B), monthly budgets (C-N) and yearly total at O. Each facility has 400+ GL so the only formula that has come close to working is this one: =INDEX('sheet1'!$O$1:$O$7695,MATCH(1,('sheet1'!$A$1:$A$7695=$D8)*('sheet1'!$B$1:$B$7695=G$5),0))

Basically "O" is the yearly number I want, "A" is the facility number and "B" is the Description (which is text). After searching several posts this one returned a result on the first facility, but returned NA on the rest. Also tried CONCENATE function with A&B in sheet1 which are equal to D10&G5 in sheet2. It returned the wrong information, I was looking for "1506Net Sales" and it returned for "1506Net Income".

View 8 Replies View Related

Vba To Match Multiple Similar Data Question

Mar 12, 2009

I have a worksheet (titled "Data") that has data in columns A thru

Q. I'd like to create a command button that when activated would search column data from cell Q2 down and then identify any matching data that has 30 or more similiar matching entries. I would then need this macro to copy all of the row data across for all of the matching/applicable data and then copy it beginning in cell A2 onto a worksheet titled "Search Details". The difficulty is that filtering won't work as I need the macro to loop until it can identify any/all data that has 30 or more matching data. The type of data that would be in column

Q2 and below would be something like "Doe01/01/01", "Smith02/03/27", etc. Both the Data and Search Details sheets have headings in row A1 thru Q1.

View 14 Replies View Related

Excel 2003 :: Multiple Columns With Similar Calculation?

Jun 27, 2014

I have Excel 2003 and use the following code for 50 consecutive columns, using each column's Row 1 as the multiplier. Each column has over 20,000 rows. It works, but I'd like to know if there's a quicker and / or neater way to do it.

Range("bo2", Range("bo" & Rows.Count).End(xlUp)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
Range("bp2", Range("bp" & Rows.Count).End(xlUp)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply

View 5 Replies View Related

Multiple If Statements With Multiple True And One False

Aug 6, 2014

I am trying to get a function in a sheet where it has two possible truth outcomes and one false outcome. Both statements false statement goes back to

'Production Metrics 2'!E11/12

Here are the two If statements as they appear in the formula bar now. I need to combine them so they both work and don't cancel each out out.

=IF('Production Metrics 2'!E11/12>2500,2500,'Production Metrics 2'!E11/12) If c10+b10>=4500,0,'Production Metrics 2'!E11/12

View 10 Replies View Related

Multiple IF Statements

Nov 23, 2008

I'm having trouble joining the followinf IF Statements. They work independently, however, I cannot get them to work in the same Cell.

Any help would be greatly appreciated.

=IF(A9="YES", SUM(A15+A21),"0")

=IF(B9="YES", SUM(B15+B21), "0")

View 14 Replies View Related

Multiple If Statements....

Aug 31, 2009

Trying the following multiple if formula, however it only works for my first if statement and not the rest.

=(IF(G21="onl",WORKDAY(E21,L27,K26:K43),(IF(G21="telb",WORKDAY(G21,L27,K26:K43),(IF(G21="mrt",WORKDA Y(G21,L27,K26:K43),(IF(G21="tlr",WORKDAY(G21,L26,K26:K43),(IF(G21="atm",WORKDAY(G21,L26,K26:K43),(IF (G21="chq",WORKDAY(G21,L26,K26:K43),(IF(G21="crcdwu",WORKDAY(G21,L28,K26:K43)))))))))))))))

View 4 Replies View Related

Multiple IF Statements ..

Apr 28, 2007

Is there a way to write a forumula that read, if it's January, add column A1:A10, if it's February, add column A1:B10, if it's March, add column A1:C10 and it's April, add column A1:D10

View 9 Replies View Related

Using 2 Variables To Return Multiple Items From Multiple Sheets

Feb 14, 2009

I have a need to populate a summary worksheet using two variables to find data in two or more other worksheets.

I find writing out what I want helps some times so let me try it here.

So my variables are:

Product (there are 22 products)
Supply Less than (inset number)

These are the two criteria I want to use to produce a result.

The next issue is I have 300 stores that carry said 22 products. Each store has a unique number 0001, 0002, 0003 etc. So in a separate worksheet I have a list of the store numbers, and then the products. So each product has the store's number to the left in Column A, Column B has the product name, Column C has the quantity on hand.

What i would like to do on the summary page is select the product, and then select the supply less than or equal to 'x' and then have the stores with the selected product less than or equal to x display below.

The last part of this is then to display (data from an other sheet) on the summary page which contains the quantity of the product selected available at the warehouse for that store.

View 9 Replies View Related

IF Statement Using Multiple Variables To Give Multiple Outcomes

Feb 6, 2009

I'm looking to work out an IF statement based on a series of dates entered to give 1 of 3 possable outcomes. Where:

A1: todays date (exmaple =NOW())
B1: due date (exmaple 10/02/09)
C1: completion date (example 12/02/09)
D1: status (overdue, outstanding or completed)

D1: =IF(A1>B1,"overdue",IF(A1<B1,"outstanding",IF(C1<=>A1 & B1,"completed")))

I know the last part is totally wrong (symbols arranged in that manner), but to clarify i would like the date entered in C1 to override the other statements in the fomula to make it read "completed". If no date is entered in C1 then the formula will return either "overdue" or "outstanding" depending on the other dates in A1 and B1.

View 2 Replies View Related

Declare Multiple Subs On 1 Line Just Like Multiple Variables

May 25, 2014

I'm making a Form with multiple pages and on every page there are atleast 36 Textboxes.

[Amount] [Description] [price]

I want to run a small sub updating the price on changing the amount. My code now looks like this:

Private Sub TextBox1_Change()
Call UpdatePrice
End Sub

Private Sub TextBox2_Change()
Call UpdatePrice
End Sub

Private Sub TextBox3_Change()
Call UpdatePrice
End Sub

I have to do this on 8 pages, 24 to 36 times, which makes the code extremely long. Is there a better way to do this?

Something like:

Private Sub TextBox1_Change(), TextBox2_Change() etc...
Call UpdatePrice
End sub

View 2 Replies View Related

Combining Multiple IF Statements

Nov 11, 2009

I have attached a sample spreadsheet for your review. I think I need to combine multiple IF statements into one single statement. See column I:

There is currently a formula in Column I that looks at Column H and computes a ROUND formula based on this information. Unfortunately, this formula only works if Column J says "MB". If Column J says "Minutes" then I need Column I to compute with this formula: =IF(H2<30,0.5,ROUNDUP(H2/60,1))

How can I combine this with the current formula to get what I am looking for? Here is the current formula (for MB): =IF(H2<0.125,0.125,ROUND(H2,4))

View 2 Replies View Related

Enter In Multiple Vba If Statements

May 17, 2008

I have attached a copy of my monthly work schedule. Sorry if it looks too noobish but I am.
The question I have is on the schedule right now, it uses to of the same schedule, one is called request off and the other the final. The request off right now is where all changes are made, and they reflect on the final. I did it this way because I could not figure out how to make the cells autofill according to the day of the week, like if it was friday i needed it to fill in the shifts and normal days off automatically.

What I am trying to do now is eliminate the need for the second schedule, the request off one. I think I have figured out how to have the cells autofilled according to the day of the week, and also let the user enter in other stuff such as sick, vacation or whatever, and have it autofill if the cell is left blank, using vba. this is what i have so far, and it is working I was just wondering if it was possible to set it up for a range of cells instead of having to enter each one in manually.

View 14 Replies View Related

Executing Multiple SQL Statements At Once

Oct 4, 2008

if it is possible to use a batch query to execute more than one SQL statement at once from Excel. Something like this:

View 9 Replies View Related

Multiple IF Statements Working Together

Aug 28, 2009

Can anyone tell me what I'm trying to accomplish with the following formula:

=(IF(E3="CORP",F14*0.9,IF(E3="GOLD/SLVR",F14*0.9,IF(IF(E3="ON",IF(Q33>=192,F14*0.9,IF(Q33>=96,F14*0.95, F14))), IF(E3="OFF",IF(Q33>=288,F14*0.9,IF(Q33>=192,F14*0.95,F14)))))))

It doesn't work as written but I don't what edits I have to make to make it work.

View 5 Replies View Related

Combine Multiple IF/AND/OR Statements

Oct 16, 2009

If my input page B13 is either New Lease In-House or New Lease w/Co-Borker andmy input page B43 is - then take cell D57 in my Analysis report, mulitply it by cell B32 from Input page and divide it by 2, otherwise, take cell D57 on the analysis report, mulitply it by B42 on Input page and divide it by 4. I think I have a mess up with my brackets. This is what I have: =IF((OR('Input Page'!B13="New Lease In-House",'Input Page'!B13="New Lease w/Co-Broker"))AND('Input Page'!B43="-",'Analysis Report'!D57*'Input Page'!B32/2,'Analysis Report'!D57*'Input Page'!B32/"4")

View 4 Replies View Related

Multiple If Statements In One Cell?

Oct 14, 2013

I want to create a formula that will turn B2 in floors if A2 has /1 with its formula, Ceilings if it has /2, walls if it has /3, and M&E if it has /4. For example 456-401/2-1569 in cell A2 will populate ceilings in B2. I know I can create If statements but I can only figure out how to do this to search for one text and populate one word. I know Vlookup is possible but this spread sheet will be blank and as people populate there information I need the cells to automatically populate the data into the B column. how I can create the right formula?

View 4 Replies View Related

Copyrights 2005-15, All rights reserved