Compare multiple range macro

[deviation]

I need some help converting some formulas to a single macro.

I have a work sheet that has, we'll say 6 columns, for example I'll use 1 row, but i need it for the whole column

A thru F
I need a macro to perfrom the following functions

A1 =IF(F1>=2,"x"," ")
B1 =IF(F1>=3,"x"," ")
C1 =IF(F1>=6,"x"," ")
D1 =IF(F1>=9,"x"," ")
E1 =IF(F1>=12,"x"," ")

Any help on this would be greatly appreciated, I have searched for it, but I either can't find anything or am unsure what terms I should be searching for.

 

[hbgray]

for i = 1 to 10
' if you're doing 10 rows

if range("F" & i).value >= "2" then
if range("F" & i).value >= "3" then
if range("F" & i).value >= "6" then
if range("F" & i).value >= "9" then
if range("F" & i).value >= "12" then
range("e" & i) = "x"
end if
range("d" & i) = "x"
end if
range("c" & i) = "x"
end if
range("b" & i) = "x"
end if
range("a" & i) = "x"
end if

next i

end sub

 

[jindon]

Hi,

try the code

Sub test()
Dim a As Variant, i As Long
With ActiveSheet
    a = .Range("f1:f" & .Range("f65536").End(xlUp).Row).Value
    .Range("a:e").Clear
    For i = LBound(a) To UBound(a)
        Select Case a(i, 1)
            Case Is = 2
                .Cells(i, 1).Value = "x"
            Case Is = 3
                .Cells(i, 2).Value = "x"
            Case Is = 6
                .Cells(i, 3).Value = "x"
            Case Is = 9
                .Cells(i, 4).Value = "x"
            Case Is = 12
                .Cells(i, 5).Value = "x"
            Case Else
        End Select
    Next
End With
Erase a
End Sub

hope this helps
jindon

 

[deviation]

Jindon,

the formula works well, you are using things that I have yet to dabble with however and I'm afraid I don't understand how to modify the macro to work for the actual columns I am using

Actual columns are as follows (example column = actual column)

A = R
B = S
C = T
D = U
E = V
F = AA

I changed the code as follows but I am obviously missing something, tried using VB help , but I am still unable to comprehend exactly what is going on.

Sub test()
Dim a As Variant, i As Long
With ActiveSheet
a = .Range("AA2:AA" & .Range("AA65536").End(xlUp).Row).Value
.Range("R:V").Clear
For i = LBound(a) To UBound(a)
Select Case a(i, 1)
Case Is >= 2
.Cells(i, 1).Value = "x"
Case Is >= 3
.Cells(i, 2).Value = "x"
Case Is >= 6
.Cells(i, 3).Value = "x"
Case Is >= 9
.Cells(i, 4).Value = "x"
Case Is >= 12
.Cells(i, 5).Value = "x"
Case Else
End Select
Next
End With
Erase a
End Sub

I think it might be in the LBOUND area but not 100% there....

 

[deviation]

Okay I played with the code and finally figured it out, it wasn't in the LBOUND area... it was the column identify in the CASE statement

One final issue and I think this is all set....

I want the evalaute the cell value as >=2 not just =2, I did not mention that originally, I apologize

 

[deviation]

one other issue, how do I start it with row 2 not row 1?

 

[deviation]

by reversing the order to the check I did accomplish the >= in the CASE statements.

Still working on it starting with Row 2

 

[jindon]

Hi

try change

.Cells(i,

to

.Cells(i+1,

jindon