Data samples

A

Anders J.

Hey
I have a large set of data samples and i need to put in a function which can
tell me the errors between two numbers. I have tried with this one:
Sub ErrorTrueFalse()
'
' Macro2 Macro
'

'
ActiveCell.Select
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<(RC[-2]*0.75),FALSE,TRUE)"
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<(RC[-2]*0.75),FALSE,TRUE)"
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<(RC[-2]*0.75),FALSE,TRUE)"
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<(RC[-2]*0.75),FALSE,TRUE)"
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<(RC[-2]*0.75),FALSE,TRUE)"
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<(RC[-2]*0.75),FALSE,TRUE)"
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<(RC[-2]*0.75),FALSE,TRUE)"
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<(RC[-2]*0.75),FALSE,TRUE)"
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<(RC[-2]*0.75),FALSE,TRUE)"
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<(RC[-2]*0.75),FALSE,TRUE)"
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<(RC[-2]*0.75),FALSE,TRUE)"
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<(RC[-2]*0.75),FALSE,TRUE)"
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveCell.Offset(1, -33).Range("A1").Select


End Sub

but i get an error saying 'Application-defined or object-defined error' with
debug on the last line.

Can someone please help me fix this and make the macro go as a loop for 550
rows.

In advance thanks!!
 
J

Jacob Skaria

Try the below macro...

Sub ErrorTrueFalse()
Dim lngCol As Long
For lngCol = ActiveCell.Column To 100 Step 3
Cells(ActiveCell.Row, lngCol).FormulaR1C1 = _
"=IF(RC[-1]<(RC[-2]*0.75),FALSE,TRUE)"
Next
End Sub

If this post helps click Yes
 
G

GB

What is the purpose of using Offset followed by Range("A1")? Offset returns a
range object, therefore you should be able to do:
ActiveCell.Offset(0,3).select
as applicable, or
ActiveCell.Offset(1,-33).select

I haven't seen the ScrollColumn feature, but it if it changes your active
cell, then -33 would take you to a negative cell and would throw an error.

If you take a look in your watch window, at:
activecell.column you should be able to tell if it will go negative. Also
put a stop in at the line, run your code, and "Watch" the value of column to
see what it is before and after attempting to run that line.

As for looping, if you specifically need a 550 line loop, it looks like you
can use a for loop from 1 to 550

Add (just before the first Activecell.select):

Dim I as long

Then (after the dim statement above):

For I = 1 to 550
.... all the activecell offset etc work.

Next I before the End Sub
Anders J. said:
Hey
I have a large set of data samples and i need to put in a function which can
tell me the errors between two numbers. I have tried with this one:
Sub ErrorTrueFalse()
'
' Macro2 Macro
'

'
ActiveCell.Select
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<(RC[-2]*0.75),FALSE,TRUE)"
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<(RC[-2]*0.75),FALSE,TRUE)"
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<(RC[-2]*0.75),FALSE,TRUE)"
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<(RC[-2]*0.75),FALSE,TRUE)"
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<(RC[-2]*0.75),FALSE,TRUE)"
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<(RC[-2]*0.75),FALSE,TRUE)"
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<(RC[-2]*0.75),FALSE,TRUE)"
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<(RC[-2]*0.75),FALSE,TRUE)"
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<(RC[-2]*0.75),FALSE,TRUE)"
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<(RC[-2]*0.75),FALSE,TRUE)"
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<(RC[-2]*0.75),FALSE,TRUE)"
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<(RC[-2]*0.75),FALSE,TRUE)"
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveCell.Offset(1, -33).Range("A1").Select


End Sub

but i get an error saying 'Application-defined or object-defined error' with
debug on the last line.

Can someone please help me fix this and make the macro go as a loop for 550
rows.

In advance thanks!!
Click to expand...
 
M

Mike H

Hi,

Maybe you could condense your code to this

For x = 0 To 34 Step 3
Cells(ActiveCell.Row, ActiveCell.Column + x).FormulaR1C1 = _
"=IF(RC[-1]<(RC[-2]*0.75),FALSE,TRUE)"
Next
Range("A1").Select

Mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

macro to copy rows that contain a cell with a certain number 2
Macro that deletes and pastes filterd rows and then some 2
"=TEXT(RC[-1],"MMM")" 4
VBA Compare part of cell to another cell and then calculate 0
why won't this work 2
changing range in recorded macro 1
Linking Cells Macro 3
Link Cells & Paste Special Macro 7

Top