VBA: i have the logic but need help writing the code.

[helpsky]

hi, I attached the spreadsheet I am looking at as an example for the code. although I have the logic, i am having trouble writing a code to do the following in sheet 1:

searching through column A, find a cell that has a period in it starting from A3 (so the first one the macro should find it A5) multiply the number 1 row above it in row titled shares and price (so column b times c except I want it to be dynamic and reference the column title) and paste that value in the next available column BUT in the row where it originally referenced the cell (so D2 for this example)

logically, I just want the product of the last ticker in a set that is defined by starting with a period and ending right before the next period. so A2:A4, A5:A7,A8:A12, A13:A16 are all sets here. then I want to take that value and assign it to the title of the set which starts with a period. after that, I would like to delete all rows that don't start with a period and am left with something like what is on sheet 2.

please advise if you have any ideas on how to accomplish this!


<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:134; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {color:black;}--></style>

 

[helpsky]

Re: VBA: i have the logic but need help writing the code. please help!

sorry, thought there would be a way to attach sheets! this is what im looking at in sheet one:

name	shares	price
.fg	1	1
f	2	2
g	3	3
.gr	4	4
g	5	5
r	1	1
.abc	2	2
a	3	3
b	4	4
c	5	5
d	1	1
.rt	2	2
r	3	3
t	4	4
f	5	5

<tbody>
</tbody>

and after running the macro i should have:

name	shares	price	total
.fg	1	1	9
.gr	4	4	1
.abc	2	2	1
.rt	2	2	25

<tbody>
</tbody>

 

[Rick Rothstein]

Re: VBA: i have the logic but need help writing the code. please help!

Give this macro a try...

Sub NamesSharesPrice()
  Dim Ar As Range
  Columns("A").Replace ".", "=", xlPart, , , , False, False
  For Each Ar In Range("A2", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlConstants, xlTextValues).Areas
    Ar(1).Offset(-1, 3) = Ar(Ar.Count).Offset(, 1) * Ar(Ar.Count).Offset(, 2)
    Ar.EntireRow.Delete xlShiftUp
  Next
  Columns("A").Replace "=", ".", xlPart
End Sub

 

[helpsky]

Re: VBA: i have the logic but need help writing the code. please help!

this worked, thank you so much! I just have a few questions.. where does it state which columns to multiply? in my example it was just b times c but what if i want it to make it columns n times r which are titled shares and price, respectively? also, how does it know where to paste the product since some groups have 3 rows underlying, some have 4, etc?

 

[Rick Rothstein]

Re: VBA: i have the logic but need help writing the code. please help!

this worked, thank you so much! I just have a few questions.. where does it state which columns to multiply? in my example it was just b times c but what if i want it to make it columns n times r which are titled shares and price, respectively? also, how does it know where to paste the product since some groups have 3 rows underlying, some have 4, etc?

The way the code works is this...

1) Dots in Column A are replaced by equal signs. This divides the data into two parts... formulas display the #Name ! error and regular text constants.

2) Next, the SpecialCells function is applied to the cells beneath Row 1 (we are going to ignore the headers) and it is set to look for constants values that are text values. Special cells returns all the cells meeting this criteria within the specified range, but because the range is not contiguous, the values are returned in separate groups of cells which Excel refers to as Areas.

3) The For..Each loop is set to iterate each Area returned by the SpecialCells function one area at a time.

4) Within each area (the Ar variable is used to reference each Area as it is being processed), Ar(1) references the first cell in the Area. What my code does is to work out everything as offsets from this first cell within the referenced Area.

5) Ar(1).Offset(-1, 3) references the cell one row up and three columns over from the cell being referenced by Ar(1). So, for the first loop, Ar(1) references cell A3 and Ar(1).Offset(-1, 3) references cell D2. Next, what gets assigned to that cell is calculated. Ar(Ar.Count) references the last row in the referenced Area and from that cell the column offsets are referenced. Note that those offsets are 1 and 2... those are the cell that get multiplied together. In order to multiply Column N by Column R, your offsets would be 13 and 17 meaning you would change the first code line in the For..Each block to this (I'll assume you are placing the multiplication in Column Z...

Ar(1).Offset(-1, 25) = Ar(Ar.Count).Offset(, 13) * Ar(Ar.Count).Offset(, 17)

6) The second line in the For..Each block then takes the entire row of cells within the constant/text values Area and deletes it.

The above process is repeated until all Areas have been processed.

 

[helpsky]

Re: VBA: i have the logic but need help writing the code. please help!

awesome, this makes a lot of sense! would we be able to edit the code (maybe insert a line right after Columns("A").Replace ".", "=", xlPart, , , , False, False) to do the following (we will get the same product):

copy and paste the 2 cells being referenced into the first row and then collapse the set and then do the calculation BUT paste it with the formula (not just values)?

for example, for the first set it is now:

name	shares	price
.fg	1	1
f	2	2
g	3	3

<tbody>
</tbody>

the code would take the cells we are using 3 & 3 and paste it up into the .fg part then collapse the cells and look like this:

name	shares	price
.fg	3	3

<tbody>
</tbody>

then it would just multiply these two columns and spit out the number but the formula would be in the cell as well (=B2*C2)

please let me know if this is possible!!! thank you so much!

 

[Rick Rothstein]

Re: VBA: i have the logic but need help writing the code. please help!

Does this do what you want...

Sub NamesSharesPrice()
  Dim Ar As Range
  Columns("A").Replace ".", "=", xlPart, , , , False, False
  For Each Ar In Range("A2", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlConstants, xlTextValues).Areas
    Ar(1).Offset(-1, 1).Resize(, 2) = Ar(1).Offset(Ar.Count - 1, 1).Resize(, 2).Value
    Ar(1).Offset(-1, 3).Formula = "=" & Ar(1).Offset(-1, 1).Address & "*" & Ar(1).Offset(-1, 2).Address
    Ar.EntireRow.Delete xlShiftUp
  Next
  Columns("A").Replace "=", ".", xlPart
End Sub

 

[Antomac]

Re: VBA: i have the logic but need help writing the code. please help!

for example if column A is not blank,then i need to multiply the column B by -1.The line will have up to max 3000

Can you please help mw with the vba code for this.