help looping through multiple columns

harts

Board Regular
Joined
Apr 5, 2011
Messages
101
I have a code that is written same several times but references a different column in each one. I would like to just write it one time but reference mutiple columns. Columns S, N & X. Can this be done with using the format for a row?

Range("X" & Format(x1) - 1).Value
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,690
Office Version
  1. 2010
Platform
  1. Windows
I have a code that is written same several times but references a different column in each one. I would like to just write it one time but reference mutiple columns. Columns S, N & X. Can this be done with using the format for a row?

Range("X" & Format(x1) - 1).Value
I am not sure why you are using the Format function... isn't x1 a number? Also, you don't say what you are trying to do with the range you are asking about, leaving us to guess at what you are trying to do. Anyway, assuming you are trying to assign the same value to each cell on row x1-1 for columns S, N and X, does this do what you want...

Intersect(Rows(x1-1), Range("S:S,N:N,X:X").EntireColumn).Value = SomeValue
 

harts

Board Regular
Joined
Apr 5, 2011
Messages
101
Sorry for making you guess. What I am trying to do is have a value in the range s2:s11 go through the values in columns N S & X. Basically it finds the value that is more then the previous value and less then the current value. Then it places the value from s2:s11 next to the proper value. Does this help?


For x1 = 21 To 70
For x2 = 2 To 11

If Range("X" & Format(x1) - 1).Value < _
Range("S" & Format(x2)).Value And _
Range("X" & Format(x1)).Value > _
Range("S" & Format(x2)).Value And _
Range("X" & Format(x1)).Value > "0" Then
Range("Y" & Format(x1)) = Range("S" & Format(x2)).Value

Range("T" & Format(x2)) = Range("X" & Format(x1))
Range("U" & Format(x2)) = Range("U" & Format(x1))
Range("V" & Format(x2)) = Range("X" & Format(x1) - 1)
End If
next
next
 

Watch MrExcel Video

Forum statistics

Threads
1,122,806
Messages
5,598,188
Members
414,218
Latest member
speedbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top