Excel VBA Macro Help

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

ns81

Thread Starter
Joined
Jul 11, 2007
Messages
32
Hello,

I'm looking for some help with a macro I am working on in Excel. So far my macro leaves me with the data on sheet 1 of the attached book1.xls file. What I would like the data to look like after the macro is run is on Sheet 2.

It's difficult to explain in words but I am hoping someone with experience will see this as pretty simple. I'll try to give a representation below of what I am looking for so you don't have to look at the attached file to get an idea of what I need. The changes I need the macro to make are highlighted in red on sheet 2.

Sheet 1:


100 | 328
100 | 328
100 | 328
100 | 328
95 | 95
45 | 45
100 | 245
100 | 245
100 | 245

Sheet 2

100 | 328
100 | 328
100 | 328
28 | 328
95 | 95
45 | 45
100 | 245
100 | 245
45 | 245

Any help is greatly appreciated!
 

Attachments

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
Maybe I am reading you wrong but I don't understand what you want. :(
I cannot make heads or tails form explanation.
What does the 28 | 328 represent? the 45 | 245 I don't see any relationship to the data above or below.
Sorry :confused:
 

ns81

Thread Starter
Joined
Jul 11, 2007
Messages
32
Sorry, I knew I was explaining this poorly. The “|” character represents a column split. So there are two columns in my example. When the column B has a value over 100 (like 328) then in column A I would like the last 100 value in the series of 328 changed to the last 2 numbers of the cell to the right of it. That’s why the last 100 in the 245 series should change to 45. Does that make any more sense or did I confuse the issue more? Thanks for looking at this.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
No that makes sense, I dit get the meaning of the | but the numbers where putting me on the woirng track.
I'll do some simple tests and mail you the results asap
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
See the attached sheet, there is a button in Sheet1
I stored your sample result in Sheet3

Sheet2 will be updated with what is in Sheet1

The code is written for column F to contaain the first value and G the second one, if you alter the layout you will have to edit the vba code too

I dit not sort Sheet1 first and assume that column F is alsways sorted and column G too.

The thing is that I did not test on an a single entry so 55 and 99 are red too
The value foor the 100|200 is 00

See what you can do with it and if it helps you.:)
 

Attachments

ns81

Thread Starter
Joined
Jul 11, 2007
Messages
32
Thank you very much!

I should have noted that the red color was more to indicate the changes in my example then to be a part of the final code, but the color is irrelevant since the data will be saved out as a csv file.

I really appreciate your time, I had a vague idea of the logic involved but no way I could have noodled through the code myself.
 
Joined
Sep 24, 2007
Messages
869
It may be that Ițm wrong by I sow in your data the following rule:
In column G you have the total sum of something (maybe related to other columns in the worksheet), in column F you need to have the same sum, but the may sum for each row is 100, so you need to have N\ 100 rows with 100, and 1 row which will contain the N mod 100 value, but in case the 100 divides exactly by 100, then you will have only N \ 100 rows with 100 in column F.

If I'm right then in the case of N=200 (rows 16 and 17), in row 17 you need to have 100, and not 0.

I could be wrong, in this case just ignore my post.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top