Excel If statement in increments of 4 (1 Viewer)

Brianwarnock · Dec 13, 2007

unmarkedhelicopter said:
Assuming apple is just flowery decoration and if's are irellevant and that the values are okay then :- (see attached for example =INDIRECT("Sheet1!A"&ROW()*4-3))

Alternatively I could do it with an array but that would slow your spreadsheet down ... alot ...

I think apple meant apply and I don't see why the ifs are irrelevent.

Brian

Brianwarnock · Dec 13, 2007

USM
I think you probably want the Select Case or ifs after the copy to sheet2 so, plus other changes

Brian

Code:

Sub copy4throw()
Dim x As Integer
Dim lngLastRow As Long

With Sheets(1).UsedRange

lngLastRow = .Cells(1, 1).Row + .Rows.Count - 1
End With
x = 1
y = 1
Application.ScreenUpdating = False
Sheets(1).Select
Do
'If Cells(x, colno).Value =  any selection criteria?
Cells(x, 1).EntireRow.Copy
Sheets(2).Select
Cells(y, 1).Select
ActiveSheet.Paste
Select Case Cells(y, colof#).Value
Case 1
    Cells(y, columnof#).Value = "A"
Case 2
    Cells(y, columnof#).Value = "B"

'etc
End Select
   
    
Sheets(1).Select
y = y + 1
'End If
x = x + 4

Loop Until x > lngLastRow
Application.ScreenUpdating = True

End Sub

unmarkedhelicopter · Dec 13, 2007

Brianwarnock said:
I think apple meant apply

Okay I can see that ... (but why couldn't he say so ?)

Brianwarnock said:
... and I don't see why the ifs are irrelevent.

I admit I'm missing something then Brian, as I just can't see what the if's do for him. Which bit of my formula doesn't get him the values in every 4th row (that he said he wanted starting from row 1 ... to 5 to 9 to 13 ...) ?
I see your code takes the whole row but other than that can you explain it (what he wants) to me in simple terms so that I can understand ???

Brianwarnock · Dec 13, 2007

OK
what I think he wants, and what my code does, I think as I haven't any testdata

, is take every 4th row starting at row 1, 5 etc from sheet1 and copy them to consecutive rows on sheet2 then somewhere on this row there is a number which has to be converted to a letter, hence the Select Case.

Brian

unmarkedhelicopter · Dec 13, 2007

OK, If that's what he wanted the rosito's first reply would nearly have got him that, all he needed to do was trap the "not a number" bits. e.g.
=if(isnumber(a1),if(a1=1,"A",if(a1=2,"B",if(a1=3,"C",if(a1=3,"D","Error !")))),"")
or
=if(isnumber(a1),mid("ABCD",find(a1,"1234"),1),"")

Depending on whether you like simplicity or elegance. Though there is no error trapping on the second (but you'll still get an error if the number is not 1-4).

Brianwarnock · Dec 13, 2007

Although the thread was headed "Excel If......
His problem was automatically copying every 4th row from Sheet1 to consecutive rows on sheet2, well I assume it was.

Brian

USMCFROSTY · Dec 26, 2007

It was, thanks for all the help but i had the source send it to me cleaned up this time.

Excel If statement in increments of 4 (1 Viewer)

Brianwarnock

Retired

Brianwarnock

Retired

unmarkedhelicopter

Registered User.

Brianwarnock

Retired

unmarkedhelicopter

Registered User.

Brianwarnock

Retired

USMCFROSTY

Registered User.

Similar threads

Users who are viewing this thread