Cell Value after Macro Run?

I can figure it out if before run

Page 1 of 1

5 Replies - 828 Views - Last Post: 21 December 2010 - 02:48 AM Rate Topic: -----

#1 meatloaf  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 64
  • Joined: 05-September 07

Cell Value after Macro Run?

Posted 20 December 2010 - 12:35 PM

The situation:
I have two objectives, reformat a spreadsheet and calculate the value of the '1's and '2's. But the user inserts the '1's and '2's after the reformatting of the spreadsheet, which is apart of the macro. So there are no numbers before I run the macro. If the numbers were already given before I ran the spreadsheet Id know how to do that:
Dim row As Integer
Dim oeValue As String
row = 5
While Not IsEmpty(Cells(row, 4))
    If Cells(row, 5) = "1" Then
        oeValue = oeValue + Cells(row, 4).value
    Else
        row = row + 1
    End If
Wend

But now should I set a cell where the total will be to sum up the '1' values once entered? I dont know how to do that.

Is there a way to "pause" the macro so the user can insert the numbers? Will I have to make two separate macros?


Columns:
D/4 = values
E/5 = numbers the user inserts, either a 1 or 2

Is This A Good Question/Topic? 0
  • +

Replies To: Cell Value after Macro Run?

#2 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 464
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: Cell Value after Macro Run?

Posted 20 December 2010 - 01:54 PM

kinda you can loop in while cycle till there is a value in the cell.
Was This Post Helpful? 0
  • +
  • -

#3 meatloaf  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 64
  • Joined: 05-September 07

Re: Cell Value after Macro Run?

Posted 20 December 2010 - 02:25 PM

NoBrain said:

you can loop in while cycle till there is a value in the cell.


i am unexperienced in loops. i tried this, with column B/2 now being value, and it did not work.
    row = 2
    Do
        If Cells(row, 5) = "1" Then
            oeValue = oeValue + Cells(row, 2).value
        End If
        row = row + 1
    Loop Until IsEmpty(Cells(row, 2))

I think I need something where it is the cell formula summing the values instead, so if the user edits the numbers after the macro, the total values will change.

Do you have any links on how to do this?
Was This Post Helpful? 0
  • +
  • -

#4 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 464
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: Cell Value after Macro Run?

Posted 20 December 2010 - 02:40 PM

what i was thinking is:
While Cells(row, 5) <> ""
DoEvents
Wend



so what this cycle do is loop till there is some value in the cell. now you can do a simple check if the value is correct using some variable like this:
bCheck=False
While bCheck=False
'Check if the cell have value and it is numeric and exit the cycle'
'by setting variable bCheck to true'
DoEvents
If Cell(row,5)<> "" And IsNumeric Cell(row,5) Then
 bCheck=True
End If
Wend



EDIT: code edit. i put do events in the second code because otherwise your application will hang out

This post has been edited by NoBrain: 20 December 2010 - 02:44 PM

Was This Post Helpful? 0
  • +
  • -

#5 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: Cell Value after Macro Run?

Posted 21 December 2010 - 02:45 AM

no i don't think that's the best way to do it

use the worksheet change event to handle this

view this link

http://www.ozgrid.co...cros-change.htm
Was This Post Helpful? 1
  • +
  • -

#6 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 464
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: Cell Value after Macro Run?

Posted 21 December 2010 - 02:48 AM

and of course your totally correct :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1