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