Automate tasks in a macro or VBA script
I am a complete newbie. I am sorry that this question and explanation is so long, but I do not know how else to try to get an answer.
I have tried and read loads and gone nowhere fast.
I have managed to create a macro where I use an if formula to compare two columns, but my problem is the following, I have more than 1000 rows to work on, I basically compare column A to Column C looging for the higher number.
The formula I use is below. =IF(A5=0,"Preview",IF(A5=C5,"Good",IF(A5>C5,"Content",IF(A5
| Attachment | Size |
|---|---|
| compare_demo_start.xls | 47.5 KB |
| compare_demo_final.xls | 50 KB |
| compare_demo_start-Nick.xls | 73 KB |
| getfile_macro.txt | 7.6 KB |
| compare_catalog_error.jpg | 85.33 KB |
| Compare_Catalog.txt | 7.67 KB |
You can do it simplier
=IF(AND(ISBLANK(A5),ISBLANK(C5)),"",CHOOSE(SIGN(A5-C5)+2,"Preview","Good","Content"))
Compare 2 columns
Hi... before we go into VBA, I wonder if my example on how to compare 2 lists sufficient for you:
Compare 2 lists
Nick
Automate Task
Nick,
Thanks for te responce, At the moment, I have already done the compare bit, what I am looking for is a way to automate the process taht I have to carry out manually. That being the shifting of the cells after the comparision is done.
Thanks
Lawrence
Automate Task
Nick,
If you would like I can attach the document that I have created for you to look at.
Thanks
Lawrence
Automate task VBA
This shld be very easy..
add the file...
make it clear what the data looks like to start with, and what it looks like when you have finished your manual task.
Automate task
Try this:
Sub compare()
For i = 3 To Cells(65000, 1).End(xlUp).Row
If Cells(i, 1) <> Cells(i, 3) Then
If Cells(i, 1) < Cells(i, 3) Then
Range(Cells(i, 3), Cells(i, 4)).Insert Shift:=xlDown
Else
Range(Cells(i, 1), Cells(i, 2)).Insert Shift:=xlDown
End If
End If
Next
End Sub
Automate Task
Dear Admin,
Thanks for this, it works very well with the shifting down, but what happens now is that the formula that I have in column E and F is not carried out. Is there a way to have that computed after each comparison?
Thanks
Lawrence
Automate Process
Dear Admin,
I have now attached two xls worksheets, one for the original data (compare_demo_start.xls) and what it should look like when completed. (compare_demo_final.xls)
Thaks again for your help and advice.
Regards
Lawrence
Compare Columns VBA
Try this one:
Sub compare()
For i = 5 To Cells(65000, 1).End(xlUp).Row
If Cells(i, 1) <> Cells(i, 3) Then
If Cells(i, 1) < Cells(i, 3) Then
Range(Cells(i, 3), Cells(i, 4)).Insert Shift:=xlDown
Else
Range(Cells(i, 1), Cells(i, 2)).Insert Shift:=xlDown
End If
End If
Next
Range("E5").SelectRC[-4],""Preview"","""")))))"
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-4]),""Preview"",IF(ISBLANK(RC[-2]),""Content"",IF(RC[-2]=RC[-4],""Good"",IF(RC[-2]
Selection.AutoFill Destination:=Range(Selection, Cells(Cells(65000, 1).End(xlUp).Row, 5))
Range("F5").SelectRC[-4],""Preview"","""")))))"
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-4]),""Preview"",IF(ISBLANK(RC[-2]),""Content"",IF(RC[-2]=RC[-4],""Good"",IF(RC[-2]
Selection.AutoFill Destination:=Range(Selection, Cells(Cells(65000, 1).End(xlUp).Row, 6))
End Sub
- Be aware that if you insert rows or columns, this will not work any more.
Automate Process
Dear Admin,
Thanks for the feedback, I tried what you suggested but received an error.
Runtime Error '1004':
Application-defined or Object-defined error
debug.
If I click on debug, the following line is highlighted in yellow.
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-4]),""Preview"",IF(ISBLANK(RC[-2]),""Content"",IF(RC[-2]=RC[-4],""Good"",IF(RC[-2]RC[-4],""Preview"","""")))))"
The auto shifting still works fine.
Thanks
Lawrence