Alter Looping In VBA
Dec 15, 2008
Starting at cell H4 and down, I have data pasted to whatever last row, this is a helper sheet/vba thing that helps to create the Case Statement for SQL queries, we have many of them, broken into Pages. So this works great, grabs the data and puts 'data','data2'
The only thing is that in the last data cell in the loop, it leaves off an extra comma, as in:
'306','307','308','309','310','311','312',
I need help in removing that extra comma if there is nothing left to process in the loop so it would look like this:
'306','307','308','309','310','311','312'
Sub Test_Range_Loop()
Dim x As Variant
Dim rng As Range, cel As Range
Dim lr As Long
lr = ActiveSheet.Range("H" & ActiveSheet.Rows.Count).End(xlUp).Row ' find the last row from the bottom-up using H
With ActiveSheet
Set rng = .Range("H4:H" & lr)
For Each cel In rng
x = x & "'" & cel.Value & "',"
Next
.Range("I1").Value = x
End With
End Sub
View 9 Replies
ADVERTISEMENT
Oct 27, 2009
i need to be able to alter the tab which is specified in a VLOOKUP command depending upon which value is given in a drop down box.
ie if the drop down box reads 2 the VLOOKUP should search for an item reference given on tab 2 etc.
View 3 Replies
View Related
Jun 17, 2008
I have a userform called "DBQuery" and textbox in that called "Query".
What i need is a piece of code that will search the text in the box (after update of course) and Capitalise certain words (SQL ones like "SELECT, FROM, WHERE, AND, IN, LIKE, IS, NOT") and chenge the font colour blue?
View 14 Replies
View Related
Nov 12, 2009
I'm currently trying to create a rota timesheet which automatically takes shift patterns and deducts for unpaid breaks.
Basically if a shift is less than 4.5 hours long then no break is deducted
If the shift length is 4.5 - 6.5 hours then 15 mins are deducted
If the shift is longer than 6.5 hours then 30 mins are deducted.
View 4 Replies
View Related
Apr 27, 2014
The Macro Sub Match() works great, it is blazing fast.
It matches Col B on Sheet(1) to col B on Sheet(2) and returns the match from sheet(1) to sheet(2) by inserting a col D in sheet(2) and returning the match found to the row at which it was found in col B.
But I need to return not just the matching name from col B but the cells on the same matching row from col C, D, E, F, G, H ,I from sheet(1)
I do not understand to code well enough to do this
I am trying to replace this which is very slow
[Code] .....
View 4 Replies
View Related
Sep 16, 2009
I've a file that uses user respone to navigate through a number of charts like a slide show (not my work - downloaded from PHD I think). To move from one 'slide' to the next users are required to select "Ok" on a OK/Cancel Msgbox. By default the MsgBox pops up in the middle of the screen. I'd like to control the position of the MsgBox programmatically.
View 4 Replies
View Related
Sep 19, 2009
I need to changing the time data in my Time of Turn. I would like to subtract one hour from every data entry in the column Time of Turn, ie from 9:35 to 8:35, 9:55 to 8:55, etc. How would this be done with a macro for the entire workbook?
View 7 Replies
View Related
Sep 30, 2009
I have a column that has a date in it. If the day of the date = 07 (Ex: 08/07/2009 or 12/07/2009 etc etc) then I want to alter the font color of the ROW to be blue...or even just the text....
View 9 Replies
View Related
Feb 1, 2014
I have a workbook with several tabs. One tab is named for the year eg 2014. Annually, I open the workbook, rename to the new year and rename the year tab to the next year eg 2014 to 2015. All the dynamic range names update as expected. Sheet2(2014) becomes Sheet2(2015). However, a line of macro code does not change and errors out because the year does not change.
Is there a way to dynamically modify the '2014' sheet reference to reflect the renaming of the year tab eg to 2015 or to reference a helper cell named YEAR?
The cell referenced above R332C1 is dynamic.
I have tried writing the above line many ways based on internet research but no success. I suspect I can use a LEN function to actually modify the line of code but I'm not sure its even possible to alter a macro dynamically to meet my needs here.
View 6 Replies
View Related
Mar 23, 2009
i have loads of oval shapes on a sheet and i am trying to loop through them when i open the workbook and change the interior color to red. however i'm getting an error and i can't figure out why?
View 2 Replies
View Related
Nov 7, 2007
Is there a function or macro that can take a list of about 200 numbers and search for these numbers throughout the workbook, If 1 of these numbers exists anywhere in the workbook, it changes the color of the number or does something to the number where I will know this number is part of the exception list? The list of 200 numbers in non changing, however the numbers I want searched will change daily. There will also be worksheets added and taken away that contains these numbers.
View 9 Replies
View Related
Oct 24, 2008
is there any way by which you can alter the number of decimals in a custom format using just custom format process (ie no VBA).
For ex:
12 should appear as 12
12.1 should appear as 12.1
12.26 should appear as 12.3
Note 12 should NOT appear as 12.0
So point being adding the "m" is an irrelevance at this time - the key is to vary the decimal points essentially based on MOD(value,1) = 0
If you can (I'm really thinking you can't) let's extend it such that 12.1 should appear as 12.1 but 12.26 as 12.26
I know you can use [value>x] type rules but I'm guessing you can't use formulae in those rules ?
View 10 Replies
View Related
Oct 26, 2009
I would like to use a pivot table to manipulate my data. However, I need to be able to select data within my data set by a range of dates (usually a week). Is there a way to use a pivot table but reduce the data set by a user enter range of dates?
The date field is the first column in my data sheet.
View 2 Replies
View Related
Dec 24, 2008
I have a sheet used to process sales orders - about 500 orders per month x ~1.5Mb each. I want to save them as smaller files.
The sheets to save are full of vlookups linked to external sheets and tabs, other formulas, and macros - most of which do not need saving when the sales order is filed. There are lots of merged cells too.
I must save:
1. Cell values
2. Cell formats incl merged cells, borders, colour, font etc.
3. The row and column sizes
4. Print set ups - print area, margins, page setup, header footer etc - (Everything needed to reprint to same as original)
I think I need a "File save as" style Macro which opens a dialogue box for the user to nominate the destination folder (& allows the user to browse for it), and a new file name.
The original file name is "Job Sheet - Omlaw.xls"
The tab/sheet to save is "Front Sheet"
(There are two other tabs - neither of which need saving.)
All cells to be changed to "locked".
The sheet must be password protected in Excel. User to enter it - or not.
The saved file should be "write protected" if possible?
View 7 Replies
View Related
Oct 5, 2008
in my spread sheet i am trying to use the if function to compare the current time ( vb code used) to a time i enter to alter another cell.
for example
if A1(current updating time) = A2 ( time i choose) then
B1( some value ) = B2( another value)
the point of this equation is to have B2 update its value from B1 every day so today B2= 3 and B1 =4 then tomorrow B2 would = 4 etc , i just dont know how to do it.
View 9 Replies
View Related
Aug 13, 2009
I need to alter this code in order to do 2 things
1. to leave a blank line inbwteen Shop A's Apples, Shop A's Pears etc
2. I also wanted to total up the price totals....(see example below).So if the last price was in E4, the total of all those prices above would show in F5 ....
View 9 Replies
View Related
Dec 19, 2012
I have attempted to alter this example to simply check if the cells are not blank by replacing the "X" with "<>", but I get errors in the SMALL function--it looks like the IF statement is just returning an array with all of the values set to FALSE, and none of the values in the array call out a location of the next filled cell.
How could you alter your fixed XLSX file to check for non-blank cells instead of "X" specifically?
View 2 Replies
View Related
Jul 16, 2014
I need to use the Worksheet Change event in a particular sheet in a specified column which works fine if the data is already there in the sheet and then changed however,the data is in this sheet is actually a Sub-Set of a Main sheet i.e certain filtered records are being copied from Main Sheet and then copied to this IBSL Sheet.
After the data is copied I have to check each record manually and then categorize each record as Fresh , Rebooked , Cancelled , Tranch or On-Hold.....These 4 criterias are added in the Column 38 and the same thing has to be repeated in the column 40 , so when i change the data in the column 38 the same category has to be updated in the same row in the column 40..
But the problem is that the data is first copied from the Main Data Sheet into the IBSL Sheet using a Macro so then this even t gets fired and goes in the DEBUG MODE...
I need this to happen when i change the category manually..I am adding data validation at the same time while copying the data in to the TEMP sheet.
So what can be done to achieve..
View 7 Replies
View Related
May 1, 2014
I need the macro to look at cells B9:B84 on the Sheet1 tab of the Cost Template. If it finds an x I need it to copy the 3 cells to the right of the x and paste them in a template. For example if it sees an x in cell B9 it would copy cells C9, D9 and E9, open the Purchase Order to the Detail tab, then paste it to cells B3, C3 and D3. It would continue looking for an x down to B84. So if it found 5 cells with x, it would give me 5 instances of the Purchase Order with 3 cells pasted into each.
I've attached my Cost Template and the Purchase Order it needs to copy to. In the Cost Template is a macro called Create_PO. This is what I was trying to alter to make this happen. I can't seem to get it right! FYI in case it matters, I had to change the Cost Template from .xltm to .xlsm in order to upload it on this site.
View 11 Replies
View Related
Aug 8, 2014
I know how to loop for 1 to 10.
But is it possible to loop for 01 to 10?
Like:
[Code] ......
Because I am pulling data from external aplication, so the 0 is fix in a row/column.. I will need to get the row/column for the whole "01" and not only "1".
View 2 Replies
View Related
Aug 18, 2009
I am not sure what I am doing wrong with this expression. I know the code is a little messy, sorry.
View 2 Replies
View Related
Aug 8, 2009
Is it possible to write a cell formula (not a macro) to loop through a range of cells and test for a value? Something tells me that I need to use the "Offset" function.
View 9 Replies
View Related
Feb 14, 2010
How do I continue to loop through the last row and get the values to 3 differrent text boxes?
If I remember right, I think the command is Offset, 1 to go to the right one cell correct?
Sub CommandButton1_Click()
'("PowerAnalysis").cells(Row, 3).Value = textbox1.Value
'Worksheets("PowerData").Cells(Row, 3).Value = TextBox1.Value
Set WkSht = Worksheets("PowerData")
LastRow = WkSht.Range("A" & Rows.Count).End(xlUp).Row
WkSht.Cells(Row, 1).Value = TextBox1.Value
'LastRow = r
'r = ActiveCell
'Dim r As Long
'r = ActiveCell.Row
'UserForm3.Show
'WkSht.Cells(Row, 3).Value = TextBox1.Value
UserForm3.TextBox1.Value = r
End Sub
This code is wrong, but how can I get the first three records on the last row to display in three textboxes on UserForm3 titled TextBox1, TextBox2 and TextBox3.
Norie may be right, I may not have to even think about using repaint.
View 9 Replies
View Related
May 21, 2006
I have created a spreadsheet used for timber design. The spreadsheet calculates whether a specified size of timber column will support a specified load under certain conditions.
The various conditions include, load duration, service class, strength class and limit states.
Ok at the moment the user specifies the load and length of the column, then chooses a size of timber and sets the conditions mentioned above. Once this has been done the calculations i have put together on another sheet, work out whether the size of timber can support the specified load.
It is important to know that the answer for the final strength formula of determining the strength of the column should be equal to or less than 1. If this is the case then the column is ok, if the answer is larger than 1 then the column isnt safe.
Also at various points within the calculation certain checks are required to make sure certain design values satisfy certain criteria. If they dont then a new size of timber sould be tried.
What i would like to be able to do is use a loop so that the spreadsheet will use differing sizes of timber and calculate the values that satisfy the criteria and eventually find the size of timber required. The final answer of the strength calculation should be as close to 1 as possible but not over.
View 8 Replies
View Related
Nov 5, 2006
My code is supposed to generate figures to plot the changes in a riverbed profile with time. At time zero, there is a main subroutine that goes through a number of Subroutines and iterations before ending. At time t, I have another main subroutine which is slightly different but would go through most of the subroutines of tine zero. I want the code to go through for time t=0, display the values and then go through the other loops as well, displaying the values for each loop. I thought I knew what I was doing, but the displayed results showed reasonable results for the first (time zero), but the others were just replicas of the same thing. Is there anyway I could use variables with vectors i.e patty(j,i), where i could run the loop for i, and then do Next j.
View 4 Replies
View Related
Feb 8, 2013
I have the below code which converts a range to PDF and saves it on our network drive. I would like to change my code to a loop so I don't have to do so much typing. There are 68 rows between each range.
VB:
Sheets("CPS CSR Dashboards").Range("A2:K69").ExportAsFixedFormat xlTypePDF, "G:Call Center ReportingWeeklyAgent DashboardsTemp" & [ 'CPS CSR Dashboards'!M3] & ".pdf"
Sheets("CPS CSR Dashboards").Range("A70:K137").ExportAsFixedFormat xlTypePDF, "G:Call Center ReportingWeeklyAgent DashboardsTemp" & [ 'CPS CSR Dashboards'!M71] & ".pdf"
A2:K69 is the range for Agent 1 which will be saved as as a PDF M3 is the cell which contains Agent 1's name for which the file is to be named.
A70:K137 is the range for Agent 2 which will be saved as another PDF M71 is the cell which contains Agent 2's name.
View 1 Replies
View Related
Jul 6, 2013
I am trying to code something that will loop through a given set of columns. I know that if I say "For each C in R", that would mean, looping through each cell inside the Range "R", but what I want is something like this:
VB:
Dim rzRng As range, rzSmallRng As range
Set rzRng = Union(range("A:A"), range("C:C"), range("F:F"), range("AB:AB"), range("AS:AS"))
For Each rzSmallRng In rzRng
statement here...
Next
(I have a total of 30 columns that I need to put inside that Union function) . And then loop through the range as a whole set (not though each cell of that range).
I also tried the following code:
VB:
With Union(range("A:A"), range("C:C"), range("F:F"), range("AB:AB"), range("AS:AS"))
statement here....
End With
But it ended up executing the code in Range"A:A" only.
View 8 Replies
View Related
Mar 19, 2014
I have to manually drag my formula for 121 cells and then change the formula by next column and/or row number.
Currently the 2 formulas I have are:
=IF(AND($Z$4>="", $AA$4>=""),H$6,J2456)
=OFFSET(X$5,0,(ROWS($1:1)-1)*2)
After every 121 cells that I drag these formulas to, in the first formula I change from H$7 to H$7 then H$7 then H$7 and so on
In the second formula, I change from X$5 to X$6 then X$7 then X$8 and so on.And also change the ROWS from 1:121 back to 1:1
Is there a formula which I can drag and it automatically changes the column and cell reference in the 2 formulas?
View 6 Replies
View Related
Apr 11, 2014
I am looking to create a loop in a ListObject object that does calculations depending on the value of the current certain row in a column. This is the code I have so far:
[Code]....
I am having trouble with the loop; as you can see, the nested for loop will go through all the 'weight' in the column before going to the next 'grade'. I just want the loop to reference the same 'weight' that the 'grade' is on and move on. But I am not sure how to reference each 'weight' as I progress through each 'grade' in parallel.
I am aware that I can do this with regular cell references:
[Code] ......
but I am more interested in learning on how to work with the ListObject class.
View 2 Replies
View Related
Apr 28, 2007
I am using the following code in a project and would like some help to loop it until either TRUE or #N/A is found in the active cell.
View 9 Replies
View Related