Hello everyone,
I have been trying to write some VBA code to check if a cell contains a positive, or negative percentage. Based on whether it is positive, or negative I would then like to copy and paste the value into a new table on a separate sheet which has two tables. One containing all the positive values and one with all the negative values.
I wrote a nested if statement prior, but found that my if statement went line by line and would copy over negative values as the false conditions in the positive table. I need to plot this data after it is separated and with all the false conditions being plotted as zeros in my graph it is clustering everything. Although I could easily filter the data I would like to fully automate this process so I enter everything into one sheet and then the graphs are produced on another.
Basically what I need is this:
Current Table (much longer in actual)
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Variance %[/TD]
[/TR]
[TR]
[TD]-63%[/TD]
[/TR]
[TR]
[TD]41%[/TD]
[/TR]
[TR]
[TD]24%[/TD]
[/TR]
[TR]
[TD]-15%[/TD]
[/TR]
[TR]
[TD]57%[/TD]
[/TR]
</tbody>[/TABLE]
now I need to go through each row in this column and create two tables on a separate sheet that would do this
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Positive Variance %[/TD]
[/TR]
[TR]
[TD]41%[/TD]
[/TR]
[TR]
[TD]24%[/TD]
[/TR]
[TR]
[TD]57%[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Negative Variance %[/TD]
[/TR]
[TR]
[TD]-63%[/TD]
[/TR]
[TR]
[TD]-15%[/TD]
[/TR]
</tbody>[/TABLE]
The main issue with my nested if statement approach is that it does not have intelligence in the sense that once it finds a positive value and moves it to the new sheet it does not recall the last position of that value and starts going through the whole column again basically making duplicate values.
Could use some guidance new to VBA. Thanks for all the help!
Sincerely,
Olek
I have been trying to write some VBA code to check if a cell contains a positive, or negative percentage. Based on whether it is positive, or negative I would then like to copy and paste the value into a new table on a separate sheet which has two tables. One containing all the positive values and one with all the negative values.
I wrote a nested if statement prior, but found that my if statement went line by line and would copy over negative values as the false conditions in the positive table. I need to plot this data after it is separated and with all the false conditions being plotted as zeros in my graph it is clustering everything. Although I could easily filter the data I would like to fully automate this process so I enter everything into one sheet and then the graphs are produced on another.
Basically what I need is this:
Current Table (much longer in actual)
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Variance %[/TD]
[/TR]
[TR]
[TD]-63%[/TD]
[/TR]
[TR]
[TD]41%[/TD]
[/TR]
[TR]
[TD]24%[/TD]
[/TR]
[TR]
[TD]-15%[/TD]
[/TR]
[TR]
[TD]57%[/TD]
[/TR]
</tbody>[/TABLE]
now I need to go through each row in this column and create two tables on a separate sheet that would do this
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Positive Variance %[/TD]
[/TR]
[TR]
[TD]41%[/TD]
[/TR]
[TR]
[TD]24%[/TD]
[/TR]
[TR]
[TD]57%[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Negative Variance %[/TD]
[/TR]
[TR]
[TD]-63%[/TD]
[/TR]
[TR]
[TD]-15%[/TD]
[/TR]
</tbody>[/TABLE]
The main issue with my nested if statement approach is that it does not have intelligence in the sense that once it finds a positive value and moves it to the new sheet it does not recall the last position of that value and starts going through the whole column again basically making duplicate values.
Could use some guidance new to VBA. Thanks for all the help!
Sincerely,
Olek
Last edited: