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.
Code:
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
Code:
For i = 1 To 6
If "IH" & i = vbNullString Then
Else
MsgBox "IH" & i
End If
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 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?
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.
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:
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.
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.
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.
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.
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.
Ex:
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 changing...so 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 manually..as 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...
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:
Code: Dim WB as Workbook Dim WS as Worksheet
Set WB = ThisWorkbook For i = 1 To WB.Sheets.Count Set WS(i) = WB.Sheets(i) Next
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.
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:
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:= _ strDataRng
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).
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).
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".
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.
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.
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
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.
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.
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))
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.
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")
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?
