Quote:
Originally Posted by shanemarkley
As a small follow up, would it be possible to highlight if there is actually a sales price?
|
You could do that by changing:
Code:
.Range("K6:K" & LRow).ClearContents
to:
Code:
With .Range("K6:K" & LRow)
.ClearContents
.Font.ColorIndex = xlColorIndexAutomatic
End With
and changing:
Code:
.Range("K" & j).Value = Trim(Split(StrData, "$")(1))
to:
Code:
With .Range("K" & j)
.Value = Trim(Split(StrData, "$")(1))
If Trim(Split(StrData, "$")(UBound(Split(StrData, "$")))) <> Trim(Split(StrData, "$")(1)) Then .Font.ColorIndex = vbRed
End With
and using whatever colour you might prefer if you don't like vbRed.
Quote:
I get the following error:
"Run-time error '9': Subscript out of range"
Clicking on Debug points to the line: "StrItem = Split(StrData, " ")(0)"
|
The code runs without error for me, unless I add an empty dummy line to the data file. You could force the macro to ignore such errors by inserting:
Code:
On Error Resume Next
before, say:
Quote:
|
the items marked as "SLO" where there LCB# is located is special pricing cases that will have to be ignored as there pricing comes directly from the liquor reps. These will just have to be updated manually. Is there a way that is "SLO" is the LCB# just to skip over that field instead of blanking out the retail price?
|
Not without a fair bit of re-coding, as the macro starts of by clearing the entire column in preparation for the new data. That's currently done by the line:
Code:
.Range("K6:K" & LRow).ClearContents
In any event, when I reviewed the data, I found that your 'SLO' items all seem to have valid LCB# values - and are at least sometimes cheaper than your liquor rep prices (e.g. Cordial Soco & Lime SLO $20.97 -vs- 3337 SOUTHERN COMFORT AND LIME 750 ML $17.99. It appears these are the same product).
Since it's possible to obtain Adobe Acrobat Pro 8 as a free download (
http://www.techspot.com/downloads/46...at-8-free.html - note the serial# mentioned there) and that program can be automated via VBA, it would be possible to write a macro that automates IE to download the file, then automates Acrobat to export the data to a text file that the macro I've already provided can process. Quite possibly, re-coding would allow the export to text to be skipped too.
Acrobat Pro installs as a COM add-in.
For automation code, see, for example:
https://www.msofficeforums.com/excel...html#post35801