Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Find next value

  Asked By: John    Date: Nov 26    Category: MS Office    Views: 830
  

I am new to Excel VBA but have been using Excel for a few months and
hope you guys can help with a problem. I have a spreadsheet from which
I want to extract weekly totals. I can use Find to pick up the next
occurrence of a field and manually extract the information, but I
would like to use VBA to do this automatically. I have used the macro
recorder but this doesn't seem to help much.

Each row in my spreadsheet contains a reference and a balance (plus
other insignificant fields) within each week of the year. So, for
example, row 1 has a header "Week 1" (in cell A1); rows 2 - 47 each
contain reference (col A) and balance (col E); row 48 has the sum of
all the preceding balances (cell E48) and the word "Complete" in cell
H48.

Row 49 has a header "Week 2" (in cell A49); rows 50 to 107 with ref
and balance; cell E108 the sum of balances E50 to E107 and "Complete"
in H108. And this sequence repeats for each week of the year with
avariable number of rows each week.

What I want to do is to pick up the Week number from A1 and dump it
in, say, cell A1 of a separate sheet (called Summary). I then want to
find "Complete" and pick up the value 3 cells to the left (i.e. the
total in E48 and dump that in cell B1 of the Summary sheet. I will
then go to the next line (column A) to pick up the next week number
then look for the next occurrence of "Complete" to pick up the value 3
cells to the left so that my summary sheet contains each week number
is column A and the total balance for that week in col B. I am happy
using offset to move around the sheet but how do I find the next
occurrence of "Complete" and dump the data to another sheet using VBA?

I don't need a script writing for me - I just hope somebody can point
me in the right direction. I have tried looking through various sites
but, having spent a month on this, I can't find exactly what I'm
looking for.

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Jae Williams     Answered On: Nov 26

I am very pleased you want to try to do this yourself. It is just the kind of
exercise to learn on.

I would suggest
- step through each row  on the sheet using a for - next loop for the row
number,
- look at H & Row Number.
If it is "Complete" then
copy E & row Number to H & Row Number
move to the target sheet,
paste in the relevant cells
increment the target row number
switch back to the source sheet

You could speed up this process if for example there are always 48 weeks but
it probably does not matter much. Try to make something that works, even if
it is not the prettiest solution.

Some things you should try to do.

Put "Option Explicit" at the top of the module. This forces you to declare
(Dim) every variable. It is a valuable way to catch typos, especially if you
go Debug/Compile before you try to run it. I always do these two things.

Also if you declare the type of each variable it helps intellisense to work
and clue you in on the methods and properties you can use.

Frex: Dim wsSource as Worksheet


Use the range object to refer to cells rather than selecting the cells.
Doesn't matter for this exercise but when you get to do other things it speeds
them up no end as well as making your code more precise. So use Range(E &
RowNum).value rather than

Selection.offset(3).Activate
Selection.value

If you get stuck please post your code here and we will help.

 
Didn't find what you were looking for? Find more on Find next value Or get search suggestion and latest updates.




Tagged: