Make macro copy some data, alter or ignore/delete other data?

Questions & Suggestions
1

Hi, I'm trying to get a little more help on this macro that I'm using to copy data from a website to my spreadsheets. Here is a video that shows you where I'm at & what I'm trying to do.

The macro currently copies the numbers on the website and changes the red colored numbers to negative when it pastes them. I need to add a condition so that it changes only the red numbers that begin with a "$" or end with a "%."

Here is the macro itself if you want to take a look. Any help would be greatly appreciated.
copy cells mod 2.0.kmmacros.zip (9.5 KB)
copy cells mod 2.0.kmmacros (35.9 KB)

2

Hi,

I don't have excel available so I cannot test it, but I would try the following:

remove the following from the first loop (this code removes the indicators $ and % which you need to be able to make the distinction between the negative and not negative numbers):

  set curValNew to curVal
  if (curVal starts with "$") then set curValNew to (text 3 thru -1 of curVal) as number
  if (curVal ends with "%") then set curValNew to ((text 1 thru -3 of curVal) as number) / 100.0
  if (curVal ≠ curValNew) then
    set curVal to curValNew
    set its value to curVal
    set curValType to class of curVal
  end if

Add the following code (almost the same as the removed part) to the second loop (after the comment " --» Loop Thru ALL Cells In Selection
") directly after the following line:

set curValType to class of curVal

(By moving the code down, you can still make the distinction between the possible negatives and other red numbers)
Code to add:

set posibleNegative to False
set curValNew to curVal
if (curVal starts with "$") then
   set curValNew to (text 3 thru -1 of curVal) as number
   set posibleNegative to True
else if (curVal ends with "%") then
   set curValNew to ((text 1 thru -3 of curVal) as number) / 100.0
   set posibleNegative to True
end if
if (curVal ≠ curValNew) then
  set curVal to curValNew
  set its value to curVal
  set curValType to class of curVal
end if

Finally change:

    if ((cellColorRGB = redRGB) and (curVal > 0)) then

into

    if ((cellColorRGB = redRGB) and (curVal > 0) and posibleNegative) then

Complete code:
red 2.zip (4.9 KB)
(edited because I forgot to remove the first portion of code)

Hope this helps,

René

3

To remove the unwanted cells in your sheet, try this:

Add:

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--» remove unused cells
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
tell active sheet
delete range range "E9:F29" shift shift to left
delete range range "C9:C29" shift shift to left
end tell

Direct before the following code:

end tell

if (numRedCells > 1) then

Again: I couldn't test it, but it should work.