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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,958
Office Version
  1. 2019
  2. 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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,991
Messages
5,856,689
Members
431,828
Latest member
kARTIK12345

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