Locating Important Columns In A Table Of Raw Data - Setting Variables Using A Loop
Feb 22, 2012
I need to be able to locate some important columns in a table of raw data (the column locations are not fixed).
I would like to identify the locations (based on the heading values in Row 1) and store them as Public variables. The Match function works fine for this, however I'd like to make a simple loop to set these variables (opposed to repeating the function for each).
In the example below, I can't figure out how to reference "List1(Count)" as the name of the variable I'm trying to set.
Code:
Public Field1 As Long, Field2 As Long, Field3 As Long 'The column numbers will be stored here
Sub FindFields()
Dim List1(3), List2(3)
Dim Count As Long
'Public variables (declared above)
[Code] ......
So after running FindFields(), the Test1() macro should give "1 - 2 - 3" (for example) as the locations of the fields in Sheet1. But currently this doesn't work.
View 2 Replies
ADVERTISEMENT
Oct 24, 2008
How can I locate 1000 or the closest number from column 5, and all the data to the left of it? I have tried all the commands I could find, and nothing has worked for me.
This is the data I want to pull from the table.
1.463149.6271.0440.985999.87
View 11 Replies
View Related
Jul 10, 2014
I have a worksheet which is working nicely, but I want to extract a couple of the most important values and display them in the whitespace on the right of the table. Because of their importance I would like to do so in a larger font than is used in the rest of the workbook, but without increasing Row height to accomodate it. Essentially I'd like to do a center across selection, but vertically not horizontally but can't find a way to achieve this.
View 1 Replies
View Related
May 14, 2009
I've got a table similar to the below (but with a few more columns & rows). I need 2 look at 2 cells one contains a $value (say $75,000) the other contains a code (say C). I need it to return the correct value - in this example 3.00%.
This is going to be too messy to use a vlookup, I'm sure there is something better. Is this where you use Match or Index ... both of which I don't understand how they work. Or something else ?
ABCD
$ 1 $ 50,000 1.00%1.50%2.00%2.50%
$ 50,001 $ 100,000 2.00%2.50%3.00%3.50%
$ 100,001 $ 250,000 3.00%3.50%4.00%4.50%
A B C D
$1 $50,000 1.00% 1.50% 2.00% 2.50%
$50,001 $100,000 2.00% 2.50% 3.00% 3.50%
$100,001 $250,000 3.00% 3.50% 4.00% 4.50%
View 9 Replies
View Related
Mar 6, 2012
I've been looking at code a lot here and at the end I always see people ending their VBA code by setting the variables to nothing. Why is this done? Don't all the variables in the sub automatically get trashed when the sub ends?
So what would be the difference of me doing:
Code:
Sub emailUser(strSubject As String, strMessage As String)
'Allows you to email the user if an error occurs instead of giving a messagebox
'and stopping everything. This way if something can not be checked out, you know
'what happened and can redo it the next morning (or fix the error if need be)
Dim olApp As Object 'Outlook.Application
Dim Msg As Object 'Outlook.MailItem
[Code] .....
vs
Code:
Sub emailUser(strSubject As String, strMessage As String)
'Allows you to email the user if an error occurs instead of giving a messagebox
'and stopping everything. This way if something can not be checked out, you know
'what happened and can redo it the next morning (or fix the error if need be)
Dim olApp As Object 'Outlook.Application
Dim Msg As Object 'Outlook.MailItem
[Code] ........
View 2 Replies
View Related
Oct 24, 2013
I have put together the below macro to take data from an Excel spreadsheet and place in a word documents (using bookmarks.
What should happen is it should open up the correct word template depending on what data is in column E (Servicecode) and fill in the bookmarks with the data from excel.
It all works fine and goes through the loop and saves, inputting the correct data into the bookmarks but after looking at the saved templates and scrolling through using F8 the IF ELSE does not seem to work. It CALLS theBearSSR macro rather than the relevant macro e.g. word in E2 is Cele, the macro should call the CeleSSR macro (which opens the correct template).
I don't think i have declared my DIM Servicecode correctly... I can't seem to figure out how to make the IF ELSE condition look at the Servicecode and then go to the correct sub routine (the difference between them is the template that is opened).
HTML Code:
Sub Celebration()
Dim departuredate As String[code]......
View 5 Replies
View Related
Aug 12, 2006
With Wb.strMyBookINT
Set S70Wscopy = Sheets("s70 pivot data") 'set s70 pivot data sheet for kpi 44 s70 pivot data
Set IMFWscopy = Sheets("imf pivot data") 'set imf pivot datasheet for kpi 44 imf pivot data
End With
With Wb.strMyBookEXT
Set IMFEXWscopy = Sheets("imf ex") 'set imf ex sheet for kpi 15 imf ex
End With
correct syntax to set these sheets?
View 9 Replies
View Related
Sep 19, 2013
I need to create "Define Name" references for 70 columns of data so that I can establish Parent/Child relationships in a data validation list. Is there a quick way to do this or do I need to go through the same steps 70 times (i.e., once for each column)? My concern is that the current order of my columns might change too, and I don't want to have to recreate the "Define Names" every time it does.
View 1 Replies
View Related
Jul 28, 2014
i have a variable that will be the same in every module within a workbook. i have tried playing with Global, Public, Conts, etc. but get compile errors.
essentially this is what i want... i just want to set the dte globally so i dont have to set it in each module.
Code:
Global dte As Date
If Weekday(Now()) = vbSunday Then
dte = Date - 2
Else
dte = Date - 1
End If
View 3 Replies
View Related
Nov 29, 2013
I'm interested in creating a two variable data table with the two variables in the top two rows.
I'm aware that two variable data tables are usually done in an array with variables in the top row and leftmost column. However, as there are multiple outputs to the variations that I want to make it makes sense to have the variations in the two top rows.
View 8 Replies
View Related
Feb 9, 2010
Is there a way to test out a formula using Excel that has more than 2 variables? (If only 2 variables, I would use a simple data table). The formula I am testing has 50X50X20 variables (and as a subset, each variable has 2500 lines of assumptions!). I don't need to see all the results, only the result that produces the highest result. For simplicity (if someone has an answer) let's assume 3 variables are A1, A2 and A3. Formula is in A4 and formula is A1+A2+A3. Assuming A1 can be 1 to 5 and A2 same , A3 same, the long hand calc is for me to run a data table using variables in A1 and A2 the fixing A3 (as 1)..note maximum result....then run data table again using variables in A1 and A2 and changing fixed value in A3 to the number 2...and so on and so on....in this simple example I know the max result would be 15, but much more difficult in my spreadsheet test.
View 9 Replies
View Related
Aug 26, 2008
I was trying to use the VBA code to generate a data table:
Private Sub createDataTable(WS As Worksheet, initialRow As Integer, numCol As Integer, numRows As Integer)
Dim initialCell As Range 'specify the upper left cell
Dim RefCell As Range 'the reference cell on the caculator sheet
'activate source sheet
ActiveWorkbook.Sheets("Calculator").Activate
Set RefCell = ActiveSheet.Cells(2, 3)
WS.Activate
Set initialCell = ActiveSheet.Cells(initialRow, 1)
initialCell.offset(numRows, numCol).Select
Selection.Table ColumnInput:=RefCell
'the calculation should be automatic, if not, then calculate
WS.Calculate
End Sub
and it kept giving me an input celll not valid error on this line: Selection.Table ColumnInput:=RefCell the thing is, I have defined RefCell as a range object, so it should be OK rite?
View 4 Replies
View Related
Mar 20, 2008
For what I'd like to do, the attached workbook actually represents 3 worksheet. one input sheet and two output sheets (output1 and output2)
I plan to use command button " CommandButton1" on input sheet to start the calculations
Here's what I want to do after pressing the " CommandButton1" :
1. start with case 1 and find the value of Input A to Input J from input table using Vlookup and paste those values to respective palceholder for inputs in blue colour area above the input table for each input.
2.for empty cell in the input table paste 0 (zero)
3. after this will give results for case 1 inputs on sheets "output1" and "output2" on basis of formulas used for each output
4.then copy the results for case 1 inputs from sheet "output1" and "output2" and paste them on "input sheet " in Output table (its below Input Table) next to case1
5.Do this for all cases (in actual workbook there will be more than 100 cases)
View 9 Replies
View Related
Sep 17, 2009
Setting value in For Each statement stars infinite loop. I am trying to get the following script to work
View 2 Replies
View Related
Apr 27, 2012
Sheet1
DEPARTMENTLOCATION POSITION TITLEPOSITION NUMBERCODEKXNJCEO34500A3100KXNJCEO34501A3200DXDLMGR42001A5600DXDLMGR42002A5700NXNLCHIEF23001A1200
Data Range: A1:E6
Sheet2
DEPARTMENTPOSITION NUMBERLOCATION POSITION TITLECODEKX34500ANJCEO3100KX34501ANJCEO9100DX42001ADLMGR5600DX42002ADLMGR5700NX23001ANLCHIEF1200
Data Range: A1:E6
What I need is a formula (Not a VBA), is to reconcile both sheet2 and sheet1 ensure that the codes appearing for each position number in sheet1 gets updated based on the codes for each position number appearing in sheet2. So, for instance, the code for pos #34501A should be changed from 3200 to 9100 in sheet1.
The only issue with sheet2, though, is that the column number for position number could be different each time new data gets copied into sheet2 (thought the header information stays the same). I know that it can be done via Vlookup if the place of the column doesn't change each time, but I just
How to locate the correct column and row in a range of cells to search and then extract information based on certain conditions.
View 7 Replies
View Related
Jan 28, 2010
I would like to create a macro which finds data from multiple worksheets and collates them in my Master Worksheet.
I am competent with a lot of functions with Excel, however I have never used Macro's before. I have a little bit of VB knowledge, but only the very basics. I will attempt to explain my situation as clearly as I can.
Please note in your response that I am not familiar with a lot of the programming jargon. I also do not know how to actually create (or is it record?) a macro.
Finally, before I dive into it, I would *prefer* not to have to add code to the closed worksheets, but I can do this if there is no other way!.......
View 14 Replies
View Related
Aug 21, 2013
I have a workbook with a master data list including member names, member locations, member phone numbers, and various items checked out or on loan to members. I then have multiple sheets breaking down the data for quick reference. So for example Name, Member #, Location, Phone, Item A, Item B, Item C, Item D etc... I need to be able to have all the Members that are in certain cities displayed in it's own worksheet. I have played with the aggregate function, if and functions,... and I am dying. This is for a motorcycle club to keep track of who has ordered what, how much they owe, how much they've spent etc.
View 1 Replies
View Related
Sep 27, 2012
I have a pivot table with two columns: Code and Quantity. I want to loop through the Quantity column and hide all values = 0. I can do this easily by incorporating this into my code:
Code:
pt.PivotFields("Code").PivotFilters.Add _
Type:=xlValueDoesNotEqual, DataField:=pt.PivotFields("Sum of Quantity"), Value1:=0
However, I have a "(blank)" code in all pivot tables. I do this because occasionally there is no data to organize in a pivot table. Having a "(blank)" option will allow my code to work even when there's no data. Therefore, I want to keep blank visible at all times. The above code hides "(blank)", however.
The below code is what I've tried with no success.
Code:
Set pf = pt.PivotFields("code")
For Each pi In pf.PivotItems
If pi.PivotFields("Sum of Quantity").Value = 0 Then
[Code] ........
View 4 Replies
View Related
Sep 11, 2009
Is there a function to convert the data shown in table 1 to table 2 without rearranging the columns and rows? because i don't want to use TRANSPOSE. I want a function, somthing like SUMIF with OFFSET or INDEX and MATCH or any other function.
Table 1
Team 1Team 2Team 3Team 4Team 4Team 5Team 5ABABCity 12531642City 231173705City 367891125City 436251348
Table 2
City 4City 2City 1City 3Team 4BTeam 2Team 5ATeam 4ATeam 1Team 3Team 5B
View 2 Replies
View Related
May 31, 2007
This has sort of been asked before
Improving a vba function argument check loop
but is there a way to loop through a series of variables like var0...var9? You can obviously loop through and make a set of strings with the right names,
Dim I As Integer
Dim Var As String
For I = 1 To 5
Var = "Var" & I
Next I
Is there a way to use the contents of a string variable to call a variable with the same name as contents of the string? or somehow concatenate the a string with a number like
array[i]=var.[i]?
View 9 Replies
View Related
Jun 17, 2013
I am trying to loop through different SlicerCaches but it doesn't seem to work.
Here is the code, I am trying,
VB:
Test_Name = Array("[Test - Test Allocation]", "[Test 2]")
For i = LBound(Test_Name) To UBound(Test_Name)
ActiveWorkbook.SlicerCaches("Slicer_Exec_Function_Summary1").VisibleSlicerItemsList = Array("[Mercury].[Exec Function Summary].&" & Test_Name & "")
The code returns a mismatch 13 error.
When I try it without an array, it works fine.
VB:
Test_Name = "[Test - Test Allocation]"
ActiveWorkbook.SlicerCaches("Slicer_Exec_Function_Summary1").VisibleSlicerItemsList = Array("[Mercury].[Exec Function Summary].&" & Test_Name & "")
View 3 Replies
View Related
Jul 8, 2014
I am trying to define my variables with a for loop and if I run the code to the line after the first variable is defined, it shows that the variable is equal to the appropriate value, but after the for loop is done all of the variables are empty.
[Code] ......
View 5 Replies
View Related
Nov 22, 2011
I have a process where I need to search for multiple customer numbers and delete line associated with them.
My question is how do I create this process to run in a loop going through all 10 numbers?
View 1 Replies
View Related
Oct 16, 2007
i have set up some test script below!
i dont know if it is possible but can you concatinate two variables i.e in the example below can i move from Sum1 to Sum6 using the intiger stored in i as the end of the sum variable
Sub Sum()
Dim i As Integer
Dim Sum1, Sum2, Sum3, Sum4, Sum5, Sum6 As Integer
Dim Ltr As String
Dim Sum As String
Sum = "Sum"
Sum1 = Range("B5").Value
Sum2 = Range("C5").Value
Sum3 = Range("D5").Value
Sum4 = Range("E5").Value
Sum5 = Range("F5").Value
Sum6 = Range("G5").Value
For i = 1 To 6
MsgBox "The value in cell " & i "is " & Sum & i
Next i
End Sub
View 9 Replies
View Related
Sep 29, 2007
I have variables 1-6 like the following:
dDDDD1 = Format(d1, "dddd")
dDDDD2 = Format(d1, "dddd")
They correspond to a date from a cell and I just format one for "Monday" through "Saturday". In another module I'm opening a file that has worksheets named "Monday" through "Saturday" as well. I need to do the same thing to each of these sheets so I'm trying to setup a For/Next loop. I'm assuming you can't put a variable to a variable as I keep getting 'variable not defined' when I try to insert 'i'? Or am I just going about this all wrong?
For i = 1 To 6
Workbooks.Open ("J:AcctMgtITrepADIinf-v2-WE0922.xls")
Sheets(dDDDD & i).Unprotect Password:="hownowbrowncow"
Sheets(dDDDD1).Columns("C:E").Insert Shift:=xlToRight
Sheets(dDDDD1).Columns("B:B").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
With Sheets(dDDDD1)
Set RngCol = .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
End With
ConcName = "=B1&"", ""&C1"
LastRow = RngCol.Rows.Count
Sheets(dDDDD1).Range("$E$1:$E$" & LastRow).Formula = ConcName
Next i
View 2 Replies
View Related
Apr 6, 2008
I'm having difficulty with concatenation and the use of a string variable. On my attached spreadsheet I have a row called Must Fund Tasks. The binary variable in the cell is entered by a selection on a user form. If the value of the cell is "1" then that particular task must be funded. The code I developed forces solver to keep the binary value of 1 in that particular cell when looking for the optimal solution. This same row of binary variables is the row of values that I have solver change to find my optimal solution.
My problem is that I have another set of contraints based on mutually exclusive tasks. If there is a binary value of "1" in at least two of those cells then only one of the tasks may be funded and they are said to be mutually exclusive. In other words, if the user form indicated that tasks 1 and 5 were mutually exclusive then the binary values are Task1=1 and Task2=1 and the constraint would be Task1+Task2<=1. The code I've written looks for a value of 1 in the Mutually Exclusive row and if it finds one it adds the name of the corresponding "Must Fund" cell 4 rows above to a set called ExclusiveSet. ExclusiveSet is defined as a string and I'm having trouble with the concatenation, I keep geting a "+" out in front because of the intial loop when ExclusiveSet="" ( it returns "+$F$29+$H$29"). I'm also not sure if Range("CalcsStartcell").Offset(I + 15, 3).Formula = "=ExclusiveSet" is the appropriate way for me to return the value of the ExclusiveSet in the desired cell.
Sub constraint()
Dim p As Integer, CalcsStartcell As String, I As Integer, T As Integer
T = 7
I = 5
CalcsStartcell = "C16"
'setting Mutually Exclusive constraints
With Worksheets("Sheet1").Range(CalcsStartcell)
For p = 0 To T - 1
If .Offset(I + 12, p + 1).Formula = "1" Then..................
View 7 Replies
View Related
Oct 27, 2009
I think I just need a basic Nested For Loop code. I have a list of in column A, from A1:A537 and another list in column B, for B1:B50. I want to add a column that concatenates, each item in Column A to B1 (so 537 rows), then again each item in Column A to B2, another 537 rows, etc.. this loop is repeated 50 times, one for each name in column B.
View 3 Replies
View Related
Jun 27, 2014
I am having trouble defining a variable in a For loop. When I run the loop the first time everything seems to work fine, but when the loop goes back through the second time it carries its previous value with it. Here in lies the problem if the variable is supposed to be empty (not assigned a value). So, for example: 'Variable' in the code below may or may not be given a value depending on the conditions in the For i loop. If it is given a value then 'Variable' will still have that value on the next pass through the For j loop. And if none of the conditions are met for the If statement in the For i loop the second time through the For j loop, it should be an empty variable, but instead the code is reading it as having a value (from the previous pass through the For j loop).
[Code] .....
I have tried using:
[Code] .....
After the For j line, and instead of using:
[Code] ....
I tried using:
[Code] ....
and I've tried:
[Code] .....
I get an Object error with either line I use. I just want to be able to reset the variables each time through the For j loop so in the case of an empty variable, it doesn't try to calculate based on the previous value.
View 6 Replies
View Related
Dec 16, 2006
I need to write a FOR... Next loop that will loop through a range of rows, select the same cell range on each row, perform a task, then advance to the next row. I can't seem to figure out how to substitute the loop counter for the row index. For example:
For counter = 1 To 100
Range("B(counter)","F(counter)").Select
ActiveCell.FormulaR1C1 = "1"
Next counter
View 3 Replies
View Related
Aug 8, 2006
I've got this code that pulls data from multiple files into one table. the file name is in the top row, and each file's title, and two different sums display below that. Then it is supposed to loop through and display each task and the start and end date for that task within each file. After that the first loop brings it to the next column and file. The primary loop works fine, but the inner loop only seems to run once, as I get only the first result for each file. All I can figure is that maybe my row numbers aren't resetting like they're supposed to, or I'm completely overlooking something, which is likely, as VBA is by no means my forte.
Sub Worksheet_Calculate()
Dim sBook As String
On Error Resume Next
Application.EnableEvents = False
Col = 8
' Sets start column
Do
sBook = Cells(2, Col)
' References file name
TaskRow = 6 ........................
View 5 Replies
View Related