VBA: How To Program A Changing Variable Name In A Loop

May 20, 2006

Trying to code my first custom VBA function. The function is supposed to translate the words in a cell by repeatedly looping through the string and looking through a list of provided translation words.

Problem is the variable name changes with each loop, and I can't figure out how to program the name change.

Function TestTranslate(TranslateCell, ProductCell) As String

Dim t As Long
Dim TranslateCeiling As Long
Dim SS1 As String
Dim SS As String
Dim Q, R As String
Dim W As String
Dim LLRString, MLRString, SLRString As String
Dim LRString, MRString, SRString As String
Dim TArray() As String

Looks like the loop keeps defining Q as "SS" & t instead of doing the select case formula. I tried removing the Q variable altogether and making it read just "SS" & t = Replace(R, LLRString, LRString) for example and keep getting a compile error.

View 9 Replies


ADVERTISEMENT

Function VLOOKUP At VBA Program Inside A FOR Loop

Apr 17, 2009

I want to use the function VLOOKUP at my VBA program inside a FOR Loop

View 3 Replies View Related

Avoid Changing A Loop Counter Within A Loop?

Oct 24, 2009

I've worked on a solution for this thread (http://www.excelforum.com/excel-prog...-automate.html) but have been mentally challenged with how to avoid changing the loop counter in one of the loops I have used to resort an array of file names from the getopenfile dialog.

The aim of the shown code (see post 12 of the above link for attached file) is to check if the file containing the macro is included in the array returned by getopenfile while sorting the array of file names, and if so, moving it to the end of the array for "deletion" by redimming the array to exclude the last item. This problem of the open file being selected in the dialog may never arise, but... as the OP's request in the other thread was to allow two-way comparisons between numerous files, I've considered it likely enough to test for.

Here's the code I have settled for esp between the commented lines of hash symbols, which does change the counter (see the commented exclamation marks), but prevents an infinite loop (on my second try!) by using a second boolean flag of "HasCounterBeenChanged". Is there a better way of doing this? Or, alternatively (not in my thread title), is it possible to prevent the active file being selected through one of the arguments in the getopenfilename method?

View 3 Replies View Related

Changing Macro To Use A Loop

Mar 12, 2014

I'm using a macro to drag down a formula across a worksheet then again further down the worksheet which is working ok but I know there has to be a better way of doing it. At the moment every time I add a new column I have to edit the macro over and over and its getting out of hand. I know there must be a way of rewriting the macro into a loop but my skills are obviously still new.

Here's the macro:

[Code] ......

This continues over to column AO so far then I drop down a few rows and do it again:

[Code] .....

I think I need to set variables that set the row and column each time. maybe a "drag formula one column at a time from row x to row y until column header is empty then move to the new row and repeat"?

View 4 Replies View Related

Loop With Changing Variables

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

Changing Variable According To For - Next

Apr 18, 2007

Sub Macro1()
Dim blue1 As String, blue2 As String, blue3 As String, num As Integer
blue1 = "one"
blue2 = "two"
blue3 = "three"
For num = 1 To 3
ActiveCell.Value = blue & num
ActiveCell. Offset(0, 1).Select
Next num
End Sub

What I am trying to achieve is to use the appropriate "blue#" variable according to the num variable. For example, when num = 1, I want the macro to use the variable blue1. I am not sure how I should code this.

View 2 Replies View Related

For Loop For Changing Formulae Based On Other Sheet

Jul 27, 2006

I want to write a For loop so that it checks the range G2:R2 of Sheet1 for the first non-zero cell in that range. When it comes to the first non-zero cell, it will change the formula of A2 on Sheet2 to have the SUM of that non-zero cell and the next two cells to the right. Example:

SHEET1-
G2 = ""
H2 = ""
I2 = ""
J2 = "3"
K2 = "4"
L2 = "8"
M2 = "9"
N2 = "2"
O2 = "5"
P2 = "3"
Q2 = "11"
R2 = "7"

SHEET2-
A2 = SUM('Sheet1'!J2:L2) --->"15"

View 5 Replies View Related

Using Same Variable From One Loop To Another?

Mar 14, 2014

I have a lot of loops, but they are not necessarily nested within each other. Is it possible for me to use the same variable if those loops are not nested.

For example, something like this:

Dim a As Integer

For a=1 To 5

[Lines of code]

Next a

Then I want to just continue using "a" in other loops:

For a=12 To 100

[Lines of new code]

Next a

This way, I don't have to create a new variable every time. Would this be okay? Or is it hard on the memory?

View 2 Replies View Related

Using A Variable To Set The End Of A For Loop

Dec 18, 2008

Is it possible to use a variable to set the end of my for loop.

I only want the loop to continue while each row in worksheet 3 contains data

I have tried this as shown by creating a "TotalRows" Variable as shown in the code but i cant get it working.

View 10 Replies View Related

Stripping Out And Changing Time From Variable With VBA

Jan 31, 2014

I have a macro that needs to work with data including different time from different sources.some of the cells are date and some are text.

I have the following function

Public Function TidyDate(zdate) ' This is coming in as a date #01/02/03 08:00:00#
Dim TempVar1, Tempvar2 As String
TempVar1 = zdate
Tempvar2 = Left(TempVar1, 11) & "00:00:01"
TempVar1 = Tempvar2

[Code] ....

What i need is to change the time of the variable to 1minute past midnight but keep the date the same.

View 5 Replies View Related

Changing Range Selected With Variable

Jan 17, 2009

I have some code that uses offset to select a column of numbers

View 2 Replies View Related

Find Max Value In A Changing (variable Range)

May 11, 2009

I am trying to find the max value within a range (14 cells) contained in one column ("C" in this case). This range will change corresponding to the current cell. Ex. as the current cell moves down (or increases in number) the range of cells to find the Max in moves down (or increases in number) as well. The Max value will then be used as a variable in a equation but for now just getting it into a cell is fine. This seems like it would be easy but it is driving me absolutely batty!

I currently have the non-working code located in a For loop that contains other calculations as well. I've taken those calculations out since they work fine but cloud the bad part. So far I have tried:

View 3 Replies View Related

Changing Font Of A String Variable?

Nov 28, 2012

I'm taking some university classes and one of them is called Applications Programming and we have been using excel VBA. I have a project that I'm working on, and it's very basic. Basically we need to create a userform that has 3 option buttons, 4 check boxes, a couple labels and a couple text boxes. It's suppose to be a ice cream selection terminal. Option boxes for basic flavors of ice cream, check boxes for toppings, one text box for user input of number of scoops of ice cream and the last text box to output a reciept/overview of purchase.

So here's the question... I was wondering if there's a way to change the font of a single string variable, or change the font of a word within a string. My output to the saleBox(My receipt overview) is this:

saleBox.Text = ("Thanks you for your Purchase") & vbCrLf & vbCrLf & _
"You ordered " & numOfScoop & " scoops of " & flavorChoice & "ice cream" & vbCrLf & _ "Topped with: " & toppings & vbCrLf & vbCrLf & total

(not sure how the code is gonna look in the message but I'm sure you get the idea)

i want to be able to change the font for the flavorChoice or toppings variables. They are both strings.

Anyways, I know I can get away with basically what I have there and get full marks, but I like going above what the basics are. If it's kinda complicated.

I'm gonna attach a screenshot, more info is better than not enough right... nevermind its askin for a url!

*** I just now got the idea of putting another textbox over the area that would output the text I want to change and hide the border or something and then change the font of that text box through the properties ***

View 3 Replies View Related

Incorporating Variable Into Loop

Jun 29, 2013

I'm trying to use loop to activate 3 workbooks "OSB1", "OSB2", and "OSB3" and their respective sheets. "OSB1" has sheet "OSB1" in it, workbook "OSB2" has sheet "OSB2" in it etc.

The digit on the end of each OSB is the variable I am using in the loop.

What I am trying to come out with is the following 3 workbooks and sheets being activated:

OSB1.Sheets("OSB1").Activate
OSB2.Sheets("OSB2").Activate
OSB3.Sheets("OSB3").Activate

I've given up the code as I can't work out the inverted commas:

VB:
Dim x As Integer
For x = 1 To 3
OSB" & x & ".Sheets("OSB" & x & "").Activate
Next x

Is it possible to do this at all? The bit after "Sheets" is correct. It's part with the first "x" after the first "OSB" which is incorrect now...

( In my actual code I'm trying to do more than just activate the 3 sheets but need to work out the syntax for this to be able to edit the remainder).

View 9 Replies View Related

For Loop With Range Variable

Apr 28, 2014

I'm trying to understand a code from work and I can't get it. I copied only a part of the code here so please don't run it. Also, I have manual inputs in columns L and M (nodes labels such as 1, 2 and 2,3) and section labels on column N (such as BarFT3, BarFT4 etc):

[Code] ..........

What is this loop doing? I just get the first For loop: goes through every lable on colum N (from 1 to ne) but then what?

What is happening to range nudo(n,j)? How works this ">" sign between nudo(n.j) and nn?

View 1 Replies View Related

Change To Another Variable In Loop?

Jul 30, 2014

I have a list of Variables Dimentioned a Range. They are sequential; ie. A1, A2, A3, etc. How do I write the code to switch between these? Obviously, I am looking to correctly reference ("A" & aNo).

Code:
For Test = 1 To aNo
With ("A" & aNo)
.Select

[Code]......

View 9 Replies View Related

Changing Font Color For String Variable?

Apr 18, 2012

how to or if it's possible to change the font color of a string variable? or is there any workaround?

View 2 Replies View Related

Changing Vlookup Ref Column Based On Vb Variable

Jun 20, 2007

I am pasting vlookup formulas into a spreadsheet using a macro, and want to change the reference column number based on a variable generated within the code eg

= vlookup(RC1,table,i,false) where i is a predetermined variable in the code

for i=23, I need the result to be of the form

=vlookup(a1,table,23,false)

View 7 Replies View Related

Lookup Based On Variable/Changing Sheet Name

May 23, 2008

I'm trying to figure out a way to lookup a value based on a conditional sheet name. I'm finding it difficult to explain in words what I'm trying to do, so I'm going to try and describe it in a miniature example of my spreadsheet. I apologize if this is going against forum rules, this seems like the quickest way to get my question across:

The following is the way my spreadsheet's first page is set up:

ID Apr-08 Mar-08 Feb-08
1
2
3

The column labels depend on another table, which changes monthly.
The rest of the sheets (24 of them, labled "200804", "200803", etc) look like this:

ID $ amount Date paid
1
2
3

I have another table upon which the na

I'm trying to return the "Date Paid" value for each ID for each month. Each month, however, more data arrives, and the labels change. I have several dozen spreadsheets formatted in this way, and I'm trying to avoid having to change the vlookup (or other formula) manually each month for each sheet. Is there a way to make the vlookup depend on a table?

This is essentially what my formula looks like right now for cell A2:
=VLOOKUP($A2,'200804'!$A$1:$G$10000, 3, FALSE)

If I could replace '200804' with a cell reference, that would be ideal. I haven't been able to figure out how (if possible); I tried experimenting with index sheets but that went nowhere.

View 9 Replies View Related

Chart/Graph Changing/Variable Range

May 28, 2008

I have a vast database where I have linked charts. I send the data from a macro to the database and sometimes there are 1000 inputs and sometimes there are only 20.
How would i set up my charts to only graph the inputs that are present?

View 7 Replies View Related

Variable Type Changing Number Of Decimal Places

May 14, 2014

Here's what I'm doing: I'm using a macro to assign a cell value to a variable then set another cell value to the variable instead of copy/paste (because even pasting values only was affecting other formulas in the file for some reason)

The problem: in using the macro, the number being 'copied' is acquiring a few extra decimal places IE 38334.61 is the original number and 38334.609375 is what I end up with. The numbers come from a CSV with only 2 decimal places and I checked by adding decimal places in the format so it's not a formatting/visible digits issue. Since the values are hour meter readings, the extra decimal places end up with very small values outside 0-24 hrs which messes with sorting and usage %. The values are so small all of them together in a month add up to a fraction of a cent but it's one of those things that bugs my OCD by not being right.

My VBA book explains the min/max capabilities, content type, memory bits but not fiddly details like this.

So my question is this: right now I'm declaring the clipboard-substitue variable as an single, is there a different one that would work better without adding anything? Criteria are: numeric, 6 digits before the decimal, 2 after, all positive values. (Ie 123456.12)

EDIT: fixed my senior moment.

View 6 Replies View Related

Good Way To Reset Variable Each Loop To Empty?

Apr 15, 2012

I'm using a variable to loop down a column of data. Each time it loops the variable may or may not perform an operation - it will if there is something in the cell but won't if the cell is empty. It writes the answer of the operation to an ajoining cell, before looping again.

The issue is that if the row cell is empty, what's written to the ajoining cell is the pre-existing value of the variable, because its value hasn't changed.

how best to 'empty' the variable each loop to ensure either that the correct value or nothing at all is written to the ajoining cell?

View 5 Replies View Related

Loop The Procedure With The Variable Number Included In The Checkbox Name

Nov 21, 2006

I'm creating a user form that will have 10 checkboxes on it. Depending upon certain conditions being met elsewhere in the workbook, I would like to populate the checkboxes' captions with data from the workbook.

My question is, can I loop the procedure with the variable number included in the checkbox name (well, more to the point, HOW can I loop the procedure...)?

I would like to do something like this:

Dim a As Integer

For a = 1 To 10

With Worksheets("Hi-Tech")

If .Cells(a + 1, 2).Value "" Then
chkHiTech & a.Enabled = True
chkHiTech & a.Caption = .Cells(a+1, 2).Value
lblHiTech & a.Enabled = True
lblHiTech & a.Caption = .Cells(a+1, 2).Value
etc.

View 6 Replies View Related

Macro Loop To Check Several Conditions And With Variable Increments

Oct 20, 2006

Excel file attached! I need a macro to do the following:

Start with cell F4 and read the increment value from cell C4. Then add this with F3 and display the result. Continue with the same increment until it reaches the value equal to cell B4. Then read the increment from cell C5 and do the same until value equals cell B5. Repeat the same step until it reach the value equals B7. I did manually in the column F4 to F28. Moreover, the cell increment will change according to the variable in A2. In this case it is 25 and got 25 values to fill the column R.

View 8 Replies View Related

Looping: Run The First Macro An Error Pops Up Saying That A Variable Within The Loop Is Not Set

Mar 27, 2007

the following code should determine whether the searched value can be found in more than just one row and than enlist certain values from each of those rows in ComboBox4 using a loop. Then I have a second macro which would assign appropriate values from a Sheet to other text boxes whenever one changes the value of the ComboBox4:

Private Sub ComboBox2_Change()
Dim vFind
Dim Firstaddress
Dim rFound As Range
Dim wsName As String
Dim SrchRng As Range
wsName = Me.ComboBox1.Value
With Worksheets(wsName)
vFind = UserForm2.ComboBox2.Value
Set rFound = .Range("B1")
Set SrchRng = .Range("B:B")
Option Explicit
End With...............................

While trying to run the first macro an Error pops up saying that a variable within the loop is not set. I've got no idea how to fix it

View 9 Replies View Related

Copy Information From Variable Workbooks To Master File Then Loop?

Apr 30, 2014

What I have is a master file that needs to pull info from other sources (a simple copy and paste)
those files are called Inventory_xxxx.xlsm . Inside the master file, there are cells with the number of the inventory.

What i need is a way to tell excel to check what number is in the cell, open the files with that number, get the info from the files and paste it back in the master file... and the part that i consider the trickiest, a way to loop it.

For example, lets say i have 2248 in cell A10, it should open Inventory_2248.xlsm copy the contents from cell N4, O4, P4, Q4, R4 and S4(Inventory File) into N10,R10,S10,T10 and U10 (Master file) respectively then in A11, there could be a 2250 so it should open the 2250 file get the info, paste it and so on.....

Now I don't even know if this is do-able or just impossible, been fiddling with various codes to no avail.

View 4 Replies View Related

Changing Hard Coded Column References To Variable References

Oct 22, 2009

I have a number of statements within the Sheet Event Code (Excel 2007). Three times lately I have added a column and had to go back into the code and find all of the references that needed changing to reflect the new column.

I have been working on this for a couple of days and even tried EE, but to no success.

I have read that Defined Names / Constants should be used as often as possible, but even trying that, the VBA code errors out or "hangs up". Even within Bill Jalen's book (VBA and Macros 2007), there is nothing that addresses this, especially using Intersect.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

On Error GoTo mEnd
Set rng = Sheets("Log").[F14:F10000]
If Not Intersect(rng, Target) Is Nothing Then
If Target = "" Then
With Sheets("Log")

View 9 Replies View Related

Loop Column & Display Variable In Other Column

Dec 6, 2006

on the syntax of things in VBA. here's a description of what i'm trying to do:

1) loop through a particular column ("N"), and compare the values (if not blank) with corresponding cells in another column ("V"). i.e. - comparing N1 to V1, N2 to V2, etc...

2) run If, ElseIf statements to display appropriate values in different column ("O"), but on the row that corresponds to the row the values in Step 1 are compared. i.e. - if N1 > V1 then cell O1 = value; if N2 > V2 then cell O2 = value; etc...

here's an example (but obviously not in proper syntax). hopefully someone can help me convert it to Excel VBA:

Function CalculateFR()

Dim Col1 As Column = ColumnN
Dim Col2 As Column = ColumnV
Dim Col3 As Column = ColumnO
Dim Cell As Cell
Dim IndexValue As Integer

For Each Cell In Col1
If Col1 > Col 2 Then
IndexValue = 5
Else If Col1 < Col2 Then
IndexValue = 4
End If
Next Cell

If Not IsEmpty(Cell) Then Col3 = IndexValue

End Sub

View 7 Replies View Related

Loop Within A Loop (repeat The Loop Values)

Mar 31, 2008

With Sheets("regrade pharm_standalone")
For Each r In .Range("standaloneTerritory")
If r.Value = "X101" Then
r.EntireRow.Copy
Sheets("X101").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues
End If
Next r
End With
-------------------
I need to repeat this loop for values from X101 to X151. In all cases, the sheet name is equal to the value I'm looking up (eg: value = X102 goes to sheet X102).

I have a named range called 'territories' that contains the list of X101 -> X152.

I'm hoping to make the code perform the loop for each of the territories without my having to copy & paste and change the 'X101' 51 times as this would seem a rather silly thing to do!

View 9 Replies View Related

Same Program, Different CPU, Different Results

Aug 27, 2008

There is a machine in our office that is running the same software as my machine. (XP SP3, Office 2k7, All MS Updates)

On my machine, as well as most others in the office, all the code works fine. On another machine, strange issues arise.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved