adding a variable to a variable in a loop

gtopmafia

New Member
Joined
Jun 8, 2018
Messages
23
Hi Guys Im trying to make a Loop function for the following I want to make the payPer iterate so payPer1, payPer2 etc.

Any idea how I can do this? cheers.

pmtCount = 0
incremental = 0
x = 1
Do While pmtCount < 20
Cells(Rows.Count, 22).End(xlUp).Offset(0, 0).Select
ActiveCell.Offset(0, incremental).Select
ActiveCell.Value = payPer+(what here)
incremental = incremental + 1
pmtCount = pmtCount + 1
x = x + 1
Loop
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,486
Office Version
  1. 365
Platform
  1. Windows
It is not necessary to actually select the cells, and doing so will actually slow your code down.
Try this:
Code:
    pmtCount = 0
    incremental = 0
    x = 1

    Do While pmtCount < 20
        Cells(Rows.Count, 22).End(xlUp).Offset(0, incremental).Value = "payPer" & x
        incremental = incremental + 1
        pmtCount = pmtCount + 1
        x = x + 1
    Loop
 

gtopmafia

New Member
Joined
Jun 8, 2018
Messages
23
Hi Cheers for help so far, I need to adapt it to somthing more like this but this isnt working, CPS is a userform, and when i do "CPS.payPer & x" it literally returns "CPS.payPer1", "CPS.payPer2" etc in the excel cell and not the value attributable for example 42.

Do While pmtCount < 20
Cells(Rows.Count, 22).End(xlUp).Offset(0, incremental).Value = CPS.payPer & x
incremental = incremental + 1
pmtCount = pmtCount + 1
x = x + 1
Loop
End Sub
 

gtopmafia

New Member
Joined
Jun 8, 2018
Messages
23

ADVERTISEMENT

is there a way to maybe make a loop before to generate a variable to use in the loop i posted above? maybe that would be more efficient
 

gtopmafia

New Member
Joined
Jun 8, 2018
Messages
23
as a side not when declaring variables how can i put the declarations on the same line can you split them with a comma or?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,486
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

is there a way to maybe make a loop before to generate a variable to use in the loop i posted above? maybe that would be more efficient
I am not sure what you mean.

as a side not when declaring variables how can i put the declarations on the same line can you split them with a comma or?
You can separate them with a comma, but note that each needs to be declared explicitly.

This is the wrong way to declare a, b, and c as Integers:
Code:
Dim a, b, c as Integer

This is the right way:
Code:
Dim a as Integer, b as Integer, c as Integer

In the first example, only c would be declared as an Integer. a and b would be declared as Variant.
 

gtopmafia

New Member
Joined
Jun 8, 2018
Messages
23
further comment, sorry for spam, Ive adjusted the script to make the variable payPer have a value but I still want to loop with the +1 so how would I do this?
 

gtopmafia

New Member
Joined
Jun 8, 2018
Messages
23
I am not sure what you mean.


You can separate them with a comma, but note that each needs to be declared explicitly.

This is the wrong way to declare a, b, and c as Integers:
Code:
Dim a, b, c as Integer

This is the right way:
Code:
Dim a as Integer, b as Integer, c as Integer

In the first example, only c would be declared as an Integer. a and b would be declared as Variant.


Ok thanks
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,486
Office Version
  1. 365
Platform
  1. Windows
Ive adjusted the script to make the variable payPer have a value but I still want to loop with the +1 so how would I do this?
Once again, I am unclear what you mean. How is this different that anything we have already discussed?
If you changed anything, please post your new code and explain exactly what it is you are trying to do now.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,250
Messages
5,527,635
Members
409,778
Latest member
MagalieD

This Week's Hot Topics

Top