Macro made, just need an 'if' line at the start!

[NPgs1uk]

Hey guys,

I've made a macro, all I need is something to put at the start that says:

1. If the cell two to the left of the active cell is the same as the active cell, then move down by one cell.

2. If it is different, then I want the macro to run.

3. Then go back to step 1


There will only be about 200 rows of data, so I want it to stop moving down at around row 250.

I'm sure this is simple, but I've no idea how to do it!

Any ideas??

TYIA

[antoka05]

You can try so:



idColumn = "D"
columnNumber = Columns(idColumn.Column)

For rowNumber = 1 To 250
If Cells(rowNumber, columnNumber - 2) <> Cells(rowNumber, columnNumber) Then

'run macro
Call macro_To_Run

End If
Next

[Tristan]

Hi

Try the following

Sub NPsMacro

Do while isnot activecell=nothing
If activecell.offset(0,-2).value = activecell.value then

activecell.offset(1,0).select

Else

"Enter your macro code here"

End if
Loop

End sub

I'm assuming that if the activecell is empty then that's the end of he data you want to process and that's how the loop function will stop.
There are neater ways to write this code (for instance there's no need to actually select the cell in every loop, but don't know how you've written your bit of the macro so I've kept it pretty basic.

Let me know if it works.
Tris

[antoka05]

You can try also this:

Do While Not ActiveCell = ""
If ActiveCell.Offset(0, -2).Value <> ActiveCell.Value Then
'Call your macro
call myMacro
End If

'skip to next line
ActiveCell.Offset(1, 0).Select
Loop

[NPgs1uk]

Thanks for the reply

I tried adding them in, but they both came up with errors.

FYI, this is the macro that I want to run if the active cell does not equal (0, -2).

Warning! it's not very subtle!!!!!! :D:D:D





Sub insertblank()
'

StartCell = ActiveCell.Offset(0, -1).Address
EndCell = ActiveCell.Offset(0, -10).Address
Range(StartCell, EndCell).Select

Application.CutCopyMode = False
Selection.Insert Shift:=xlDown

StartCell = ActiveCell.Offset(0, 10).Address
EndCell = ActiveCell.Offset(0, 10).Address
Range(StartCell, EndCell).Select


StartCell = ActiveCell.Offset(0, 1).Address
EndCell = ActiveCell.Offset(0, 8).Address
Range(StartCell, EndCell).Select
Selection.Copy

StartCell = ActiveCell.Offset(0, -1).Address
EndCell = ActiveCell.Offset(0, -1).Address
Range(StartCell, EndCell).Select

StartCell = ActiveCell.Offset(0, -10).Address
EndCell = ActiveCell.Offset(0, -10).Address
Range(StartCell, EndCell).Select


Selection.PasteSpecial paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

StartCell = ActiveCell.Offset(1, 10).Address
EndCell = ActiveCell.Offset(1, 10).Address
Range(StartCell, EndCell).Select

End Sub

[Tristan]

Ok

The code below has your macro (though in a more concise form )incorporated into it.

It does what you've asked. Not sure if the way the cells shift is going to screw up the position of the cells you're trying to refer to though. Without knowing exactly whatyou're trying to do it's hard to say whether you want this effect of not.

As you can probably work out when you shift the cells down then your next referencing cell is actually being tested against a cell that was originally in the row above. Is that what you want?

If not attach an example file witrh an explanation of exactly what you're trying to do and I'll alter the code for you

Tris

Note: This code needs to be written into the sheet module that contains the data you're manipulating.


Sub insertblank()

Do While Not ActiveCell.Value = ""

If ActiveCell.Offset(0, -2).Value = ActiveCell.Value Then

Else

Rows(ActiveCell.Offset(1, 0).Row).Insert
Range(ActiveCell.Offset(0, -10), ActiveCell.Offset(0, -1)).Insert shift:=xlDown
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 8)).Copy Destination:=ActiveCell.Offset(0, -10)

End If

Loop

End Sub

[NPgs1uk]

Tris..

What I was doing was trying to piece together two sets of data. One set was incomplete, so the first set looked at the second, if the data was there then fine, if not, then it copied data over to fill in the blanks.

Thanks for the code, I've not got much of a clue how vba works so for every question I ask I get to learn a new little bit of info (like loops and skipping blanks in your examples).

Can you recommend any good online resources for beginners wanting to learn vba?

[Tristan]

So have you got the macro doing what you wanted it to do yet?

I fell nito this VBA thing by accident. I had to bury my head in a book for a while to learn it so I'll recommend Excel2003 VBA: A Programmer's Reerence by Paul Kimmel et al. Published by Wrox. Not bad for a computer book.

The only other thing you can do is practice and create little projects for yourself. This forum is very good for asking questions when you get stuck.

T

[NPgs1uk]

Yes, the macro works perfectly, thanks!

I'll look into that book, maybe I can get my company to pay for it seeing how they're the ones benefitting from it!

[Tristan]

Now that would be a trick worth knowing how to do. :D

Good luck.

[NPgs1uk]

A slightly different query, this time regarding loops. I've set up a form where the user enters data, and then a log button which copies the entered data to a different worksheet.

I want it so that the copied data is pasted into A1, and then A2, A3 etc each time the form gets filled out.

Where am I going wrong?:

Range("A25:H25").Select
Selection.Copy
Sheets("Report").Select

Range(A1").Select
If ActiveCell.Value ="" Then
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Else

ActiveCell.Offset(1, 0).Select

End If

Loop

Sheets("Front").Select
Range("A1").Select


End Sub