I want to use an if statement but in increments of 4.
The situation is this:
I have 400 rows. Row 1 is a # row 234 are text, row 5 is a # ect…. I wrote a if statement for each #-(My if statement is set up to say if the # is a 1 to mark it as "A" if a 2 "B" if a 3"C" if a 4"X"). But I only want to look at every 4 row which are the #'s. The problem is that i only want this statement to apply to every 4th cell going down the column. If I write It once then drag it down it doesn’t work. I think I have to have a nested statement within my If statement but have no idea how.

Help plz

Im not sure if I ubderstand right...but where is prob?
=IF(A1=1,"A",IF(A1=2,"B",IF(A1=3,"C",IF(A1=4,"X",""))))...drag down..

I forgot to mention that i am taking information from one sheet to another. The information on the first sheet has merged cells and no real pattern so it would be helpful if i could find a formula to just look at every 4th row without having to drag. Hard to explain I am trying offset now but no luck. My real problem is creating a formula that will look at just increments.

Can you give us an example of what the row numbers are and what column ?
e.g. C6, C10, C14, C18 ... etc.
What do you mean you want to "look at them" ??? add, average, max, min, what ???
You really should not use merged cells. In 99.9% of cases you can get away with centre across selection instead. Merged cells are a nightmare.
When you say you have 400 rows, is that fixed ? is it about 400 +/- ? will it grow ?

Their are 400 rows which i seperated to 200 each. I am looking at sheet 1 cells a1, a5, a9, ect. Some of the cells are merged (Not my spreadsheet i just analyse it) Because of the merges i can''t drag and if i unmerge the cells it creates new problems. I want to take information from sheet one and apple it to sheet 2 using the "IF". No sum or anything. I need to take the every 4th from sheet one and apply it to sheet two. The easy way to explain was How do i write a formule to nest inside my "IF" that just looks at every 4th row which would by using the "IF" put the correct letter in each column in sheet 2. I've tried offset but no luck
tks

We're nearly there, "apple it" ?
Why using "if" what does "if" give you ? if what ?
How about just getting all those values dragged down on to sheet 2
i.e. A1 > A1, A5 > A2, A9 > A3 etc.

I don't think you can do this by a formula, I think you are going to need to write code to scan down one sheet incrementing by 4 and the other by 1.
Sorry I don't have time at the moment to to this.

Brian

I don't think you can do this by a formula, I think you are going to need to write code to scan down one sheet incrementing by 4 and the other by 1.
Sorry I don't have time at the moment to to this.

BrianNo, I am confident you can do this by formula.
Admittedly, I'll cheat, but then that's how I get the results :)

Either one i'll try just give me some search tips when i use the help section.
Tks

OK you need to do something like this

Sub copy4throw()
Dim x As Integer
Dim y As Integer
Dim lngLastRow As Long
With ActiveSheet.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, colindex).Value = "yourcriteria" Then 'Caps sensitive
Cells(x, 2).EntireRow.Copy
Sheets(2).Select
Cells(y, 1).Select
ActiveSheet.Paste
Sheets(1).Select
y = y + 1
End If
x = x + 4

Loop Until Cells(x, 2) = ""
Application.ScreenUpdating = True

End Sub

Brian

Either one i'll try just give me some search tips when i use the help section.
TksSo you are just gonna ignore the questions I left you ?

So you are just gonna ignore the questions I left you ?

I didn't see any Q?

We're nearly there, "apple it" ?
Why using "if" what does "if" give you ? if what ?
How about just getting all those values dragged down on to sheet 2
i.e. A1 > A1, A5 > A2, A9 > A3 etc.Do you see the little "?" symbols ?

If turns the # into a letter which i need for codes.

Thinking about it you should use a Select Case rather than nested ifs in the code.
Mind you you haven't commented on the code.

Brian

Im not familiar with select case but will look it up

I'm not sure I understand everything with this but to identify every 4th row can't you do... =mod(row(),4)?

I tried it but it still goes in increments of one?

If turns the # into a letter which i need for codes.Eh ! You don't need the letters so why the if's and what about the other questions ? (apple and if just the value list is ok ?)

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 ...

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

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

Brian

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

I think apple meant applyOkay I can see that ... (but why couldn't he say so ?)
... 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 ???

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

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).

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

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