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 |
»
- Add new comment
- 1623 reads
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
Automate Process
Dear Admin,
After what you gave me yesterday, I started playing a little and have progressed but now I have got to a stage something strange is happening and I do not understand why and how to resolve it.
So after the error yesterday, I change the code as follows and this works for column E5, but when it gets to F5, it places the formula into cell F5 but then a Runtime error is given. If I debug the code, then it highlights line 21 shown below. I have a video attached for you to see what happens.
1.Sub compare()C5,""Preview"","""")))))"D5,""Preview"","""")))))"
2.For i = 5 To Cells(65000, 1).End(xlUp).Row
3.If Cells(i, 1) <> Cells(i, 3) Then
4.If Cells(i, 1) < Cells(i, 3) Then
5.Range(Cells(i, 3), Cells(i, 4)).Insert Shift:=xlDown
6.Else
7.Range(Cells(i, 1), Cells(i, 2)).Insert Shift:=xlDown
8.End If
9.End If
10.Next
11.
12.Range("E5").Select
13.Activecell.Formula = _
14."=IF(ISBLANK(A5),""Preview"",IF(ISBLANK(C5),""Content"",IF(A5=C5,""Good"",IF(A5
15.Selection.AutoFill Destination:=Range(Selection, Cells(Cells(65000, 1).End(xlUp).Row, 5))
16.
17.Range("F5").Select
18.Activecell.Formula = _
20."=IF(ISBLANK(B5),""Preview"",IF(ISBLANK(D5),""Content"",IF(B5=D5,""Good"",IF(B5
21.Selection.AutoFill Destination:=Range(Selecttion, Cells(Cells(65000, 2).End(x1Up).Row, 5))
22.
23.End Sub
Thanks again for your assistance.
Regards
Lawrence
Automate process
Dear Admin,
Sorry, the video file was too large to upload.
Regards
Lawrence
Automate Task
Try this: compare_demo_start-Nick.xls
Automate Task
Dear Admin,
Thanks for the responce, but I seem to have an error with the hyperlink. When I click it, I cannot save the xls file. I am taken to a different location.
Also I have been testing as well. I think that there is a problem with the range select and the Range
Selection.AutoFill Destination:=Range(Selection, Cells(Cells(E65000, 1).End(x1Up).Row, 5))
End(x1Up) seems to be throwing a wobble because it is empty.
Regards
Lawrence
Automate Task
Dear Admin,
Sorry, I pasted one of my tries into the text above. It should read.
Selection.AutoFill Destination:=Range(Selection, Cells(Cells(65000, 1).End(x1Up).Row, 5))
Thanks
Lawrence
Automate Task
Dear Admin,
I have now managed after much head ache and playing arond managed to fix the problem and it is now working like a dream, thanks to you and your help and advice.
The change that I made to resolve the problem was the following. In the last I changed the rod from 5 to 6 and this allowed it to work.
Range("E5").Select
Activecell.Formula = _
"=IF(ISBLANK(A5),""Preview"",IF(ISBLANK(C5),""Content"",IF(A5=C5,""Good"",IF(A5C5,""Preview"","""")))))"
Selection.AutoFill Destination:=Range(Selection, Cells(Cells(65000, 1).End(xlUp).Row, 5))
Range("F5").Select
Activecell.Formula = _
"=IF(ISBLANK(B5),""Preview"",IF(ISBLANK(D5),""Content"",IF(B5=D5,""Good"",IF(B5D5,""Preview"","""")))))"
Selection.AutoFill Destination:=Range(Selecttion, Cells(Cells(65000, 2).End(x1Up).Row, 6))
It is very much appreciated.
Best Regards
Lawrence
Automate Task
you're welcome...
Try the link again to the file, I have refreshed it.
Automate Task
Dear Admin,
Back again, the file could be downloaded without an issue, BUT, I have gone a fair ways further now.
This time I would like to know if there is a way to speed things up a little.
I have attached my macro called getfile. It all works very slow when it reaches the folloing part of the code.
Range(Cells(5, 1), Cells(Rows.Count, 1).End(xlDown)).Select
things went from about 45 seconds to around 5 mins to process the macro. I appreciate any feedback on your thoughts.
Thanks in advance
Lawrence
Automate Task
Dear Admin,
Sorry, but I have spotted something else that does not work, this is related to the text imports and the range set. as shown below.
Columns("A:A").Select
Selection.Cut
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Columns("A:B").Select
ActiveWorkbook.Worksheets("GetFile").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("GetFile").Sort.SortFields.Add Key:=Range("A5:A848" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("GetFile").Sort
.SetRange Range("A5:B848")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
In the rows ActiveWordBook. .... Key:=Range("A5:A848" _
and
.SetRange Range("A5:B848")
This is causing an issue, brcause all the files that I am processing will most definately have a larger range than that. Is there a way to tell it to place the rance at the last item in the column. Then also the first two csv files have two columns each and the third only has one.
Sorry if I am asking too much here but I cannot find any clear information on how to resolve this.
Also where can I find some docs that explain what the following means or interprets to?
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
I know what it does, but I would like to understand how the code was derived.
thanks agaian
Lawrence
Automate Task
Dear Admin
Have made a change that reolves my problem temporarily in a crude way, but I am very sure that it is not the best and most efficient way. I change the range to look like this.
Key:=Range("A5:A4000")
Just to let you know what I have done in the mean time.
Thanks
Lawrence
Automate task
To speed things up, make sure calculation is on manual when you enter your formula via code...
The best way for you to get the syntax is to record a macro of changing the calculation mode manually.
Automate Task
Dear Admin,
Sorry, but this is a little confusing, what is the effect of putting the calculation from auto to manual.
Will that means that I will have to manually activate or acknowledge each transaction.
Thanks
Lawrence
Manual Calculation
To speed up ur code, it will look like this:
Sub Compare()
Application.Calculation = xlManual ' turn off calculation to speed up code.
'############################
' put all your code here
'############################
Application.Calculation = xlAutomatic ' turn calculation back on.
End Sub
Manual Calculation
Dear Admin,
That did not make the process any faster at all, but that is not important, I am happy that I have take the entire process from 3 hours down to 6 mins.
Can you please advise on wo other aspects,
1) about selecting the range, I want to always select the entire range within a column.
as shown below in the row with Key:=Range("A5:A4000" _
Columns("A:A").Select
Selection.Cut
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Columns("A:B").Select
ActiveWorkbook.Worksheets("GetFile").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("GetFile").Sort.SortFields.Add Key:=Range("A5:A4000" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("GetFile").Sort
.SetRange Range("A5:B4000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
and also 2)
help understandin the code?, what doc would you recommend to read to try to understand your code.
Thanks
Lawrence
Task Automation
hihi..
1)
There is a single line of code that you might find useful:
Range(Range("A5"), Cells(65000, 1).End(xlUp)).Select
- let me explain...
"Range" can create a new range out of 2 cells assuming that the top left cell of the range is the 1st argument, and the bottom right cell of the range is the 2nd argument... (or it can be the other way round)
So in the code above, we construct a new range with A5 as the first cell, and The last occupied cell in column 1 as the 2nd cell.
- this means you don't have to always adjust the 4000 in ur example.
2) I am writing a section on how to understand recorded code:
recorded code explained
will be adding more over time..
Task Automation
Dear Admin,
Thanks for the great responce. That is exactly how I created my macro. Especially with the fact that I do not know how to code it.
So I created the macro and then went back and cleaned it up. I was only confused as to why you used the code that you provided to me.
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
I could not understand why you chose the letter i for starters, then when you used i, what did that reference to and how did you define that. I have managed to hack the results with trial and error, but I did not understand how it references the different cells.
So I really appreciate the help that you have provided to me thus far.
Thanks I really appreciate it.
Regards
Lawrence
Task Automation
Dear Admin,
Something I also forgot was the following.
The originale code from the macro that I created was
.SetRange Range("A5:B4000"
as you can see the Range starts at A5 and ends at B4000. This means that there are two columns selected. In your example you only choose only column A starting at A5. So how would I celect the two columns and start at cell A5?
Thanks
Lawrence
Here u go
B is column 2, so:
Range(Range("A5"), Cells(65000, 2).End(xlUp)).Select
"Cells" creates a single cell based on a row and a column, but it cannot use "A5" notation.
"Range" can use "A5" as an input.
so... Range("A5") is a single cell... as is Cells(65000, 2).End(xlUp)
using Range(a,b) creates a rectangle using a and b as the corners.
Automate Process
Greetings Dear Admin,
Thanks for the great support and advice you provided me with the macro code to automate the process that I am doing.
That took me 3 hrs plus is not down to a good 8 - 9 mins. So what you provided was excilent help and advice.
This is a thank you note to show my appreciation.
Your site will come very highly resccomended by me to all my colleagues and friends.
thanks
Best Regards
Lawrence
PS: I am sure that I will be back. (famous last words)
Automated Process - More Questions
Greetings,
Thanks for the support that you provided me before, but I have another question.
I have attached the content of my macro to this case, my question is the IF statement that is run on the Compare_Catalog worksheet.
This works great until it reaches line 1254. Line 1253 does the statement execution, but the next line does not run. I have also added an image
to this for you to look at called compare_catalog_error.jpg. I hope you can tell me why all the rows were not processed?
To make it a little clearer, I am not sure but I think that this is caused but the part of the macro below.
Sheets("Compare_Index").Select
For i = 3 To Cells(65000, 1).End(xlUp).Row
If Cells(i, 1) <> Cells(i, 2) Then
If Cells(i, 1) < Cells(i, 2) Then
Cells(i, 2).Insert Shift:=xlDown
Else
Cells(i, 1).Insert Shift:=xlDown
End If
End If
Next
Thanks in advance.
Regards
Lawrence
Macro help
Hi.. we have this:
For i = 3 To Cells(65000, 1).End(xlUp).Row
...the problem is that Cells(65000, 1).End(xlUp).Row is calculated at the beginning. When we add rows, it's not being recalculated.
An easy way around this is to change the 1st line to:
For i = 3 To 650000 (Or any number that you are sure it won't get past... )
Rgds Nick
Macro Help,
Nick,
Thanks for the help, that solved my problem, but I would like to understand how and why.
The confusing thing is that the error occured on line 1278 and that is way off 65000. So ho is the restriction imposed if I have not reached line 65000.
What does the 65000 decalre, 65000 characters or 65000 rows ?
Thansk again for your great help.
Regards
Lawrence
explanation
hey..
the line: for i = X to Y loops through i from the starting point: X to the end point Y.
In our code, we set an upper limit for Y, and that upper limit was the amount of rows the data contained AT THE BEGINNING of the run.
As we run the code, rows are inserted, so the total amount of rows that we need to loop through increase each time a row is inserted.
The main problem here is that at the beginning of the run, we don't know how many rows are going to be inserted, so by setting Y = 65000 all we're saying is: loop through a big enough range to guarantee we've included all the data.
Rgds
Nick
Sorry for misleading ...
Nick,
Sorry for misleading you, but I have changed the 65000 to 650000 and this has not made a difference.
The problem occurs on the second worksheet, but always at the same spot. I will try to increase the number to 990000 and see what the effect is, but I do not hold my hopes too high.
Thanks
Lawrence
You can do it simplier
=IF(AND(ISBLANK(A5),ISBLANK(C5)),"",CHOOSE(SIGN(A5-C5)+2,"Preview","Good","Content"))