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