T
trav
I am fairly new to excel. I use it to do relatively basic formulas and
charts, but I am not very good with Macros and VB.
I have been asked to help make out reports more readable. Basically we
have a query that dumps a bunch of info into an excel spread sheet.
I then have to sort it and delete some columns we don't need. up untill
this point i am fine making a macro. but after that i get stumped.
basically we have a list of items that were invoiced, so there are rows
that have the same invoice number, we sort them by invoice number so we
can see the group of items on that order. What my boss would like, is
for these separate invoices to be grouped two lines inserted and a
total sales sumed up (which i know how to do),
basically my question is, is there a way to run through a column of
data useing vb and find unique values and then enter a new line and
preform a task.
I am used to doing most of my stuff in php so this is kinda what i was
thinking
loop(runs through each row in a column, or data range b2:b230)
{
$lastnumber=someinvoicenumber //the last unique one
$newnumber=currentCellValue //current cell value
if ($newnumber=$lastnumber) //checks if they match
{ $count=$count+1 //if they match add to the counter
}
else //they don't match, start routine
{ insert two rows
add sum field into a cell on that row,
the sum would go from that cell to that cell - $count
$lastnumber = $newnumber
}
endif;
so basically i don't know how to write that in vb. I hope i explained
my self somewhat okay. Any help would be greatly appreciated!!!!!
charts, but I am not very good with Macros and VB.
I have been asked to help make out reports more readable. Basically we
have a query that dumps a bunch of info into an excel spread sheet.
I then have to sort it and delete some columns we don't need. up untill
this point i am fine making a macro. but after that i get stumped.
basically we have a list of items that were invoiced, so there are rows
that have the same invoice number, we sort them by invoice number so we
can see the group of items on that order. What my boss would like, is
for these separate invoices to be grouped two lines inserted and a
total sales sumed up (which i know how to do),
basically my question is, is there a way to run through a column of
data useing vb and find unique values and then enter a new line and
preform a task.
I am used to doing most of my stuff in php so this is kinda what i was
thinking
loop(runs through each row in a column, or data range b2:b230)
{
$lastnumber=someinvoicenumber //the last unique one
$newnumber=currentCellValue //current cell value
if ($newnumber=$lastnumber) //checks if they match
{ $count=$count+1 //if they match add to the counter
}
else //they don't match, start routine
{ insert two rows
add sum field into a cell on that row,
the sum would go from that cell to that cell - $count
$lastnumber = $newnumber
}
endif;
so basically i don't know how to write that in vb. I hope i explained
my self somewhat okay. Any help would be greatly appreciated!!!!!