Data Entry Form - Execute if value is not 0

[Qull666]

Please help.

I need the macro to execute more than once if J5 does not equal to 0.

Example: The sequence

When I hit the button,

J5 = 5, then the procedure will execute if the value in J5 does not equal to
0 once I hit the update button.
J5 = 4, then the procedure will continue to execute.
J5 = 3, then the procedure will continue to execute.
J5 = 2, then the procedure will continue to execute.
J5 = 1, then the procedure will continue to execute.
J5 = 0, then exit sub.


Thank you.


The VBA:

Option Explicit

Sub UpdateDataWorksheet()

Dim historyWks As Worksheet
Dim inputWks As Worksheet

Dim nextRow As Long
Dim oCol As Long

Dim myRng As Range
Dim myCopy As String
Dim myCell As Range


'cells to copy from Input sheet - some contain formulas
myCopy = "K5,L5,M5,N5,O5,P5,Q5,R5,S5"

Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Data")

With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

With inputWks
Set myRng = .Range(myCopy)

If Application.CountA(Range("K5:S5")) <> Range("T5") Then

Exit Sub
End If
End With


**** I think the procedure should come here *****


With historyWks
With .Cells(nextRow, "A")

oCol = 1

For Each myCell In myRng.Cells
historyWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With

End With

End Sub

 

[Per Jessen]

Hi

Try something like this:

if Range("J5").Value=0 then exit sub
Do
'Here goes your code
Range("J5").Value=Range("J5").value-1
Loop Until Range("J5").Value=0

Hopes this helps
....
Per

 

[Stan]

Dear Dave,

The code didn't work.

Thanks for helping me on this again. I would say it is a continuation (in a
way) of my earlier thread but this time is different. I am thinking of
looping the vba.

What I am trying to achieve:

Once I hit the execute button, IF cell J5 does not equal to zero, loop the
vba code (repeating until cell J5 becomes 0).

Inside Cell J5 : =COUNT($L$7:$L$15). *it is like a countdown meter.
Everytime an entry gets saved into the Database, J5 reduces by 1.

By doing this, I can use it to save multiple entries with 1 click of the
button and still use the same codes.

Thanks.


The VBA is

 

[Dave Peterson]

The code I suggested decremented the value in J1. I don't see anything in your
code that saves the workbook.

You'll have to add that save routine and this portion:

With HowManyTimesCell
'subtract one from the cell
.Value = .Value - 1
If Int(.Value) < 1 Then
Exit Do
End If
End With

would become:

With HowManyTimesCell
If Int(.Value) < 1 Then
Exit Do
End If
End With

 

[Stan]

Dear Dave,

I have tried the codes, it keeps looping and looping and won't stop. hehehe!!!

Can I send you the file? I think you will be able to see what I am planning
to do.


In the file, you will see:

Cell B8 to H27 is where the data is lined up. It can go up to more than 1
row of data.

Cell J5 is to count the number of data line from Column L8 to L27.

Cell K5 to S5 (myCopy) is where the VBA is pointed at.
(K8 to S27 replicates Cell B8 to H27 but it is Index+Small+Row, lining it up
for K5 to S5)

Currently I will have to hit the execute button more than 1 time if Cell J5
remains more than 0 in order to transfer the myCopy to Data.

What I am looking at is to execute the VBA or do a loop until J5 becomes 0
once I hit the execute button.

Thank you.

Sincerely
Stan
xl2003

 

[Dave Peterson]

No thanks to the file.

If you didn't include code that would save the file (and decrement that counter
cell), then the code will never end.

Dear Dave,

I have tried the codes, it keeps looping and looping and won't stop. hehehe!!!

Can I send you the file? I think you will be able to see what I am planning
to do.

In the file, you will see:

Cell B8 to H27 is where the data is lined up. It can go up to more than 1
row of data.

Cell J5 is to count the number of data line from Column L8 to L27.

Cell K5 to S5 (myCopy) is where the VBA is pointed at.
(K8 to S27 replicates Cell B8 to H27 but it is Index+Small+Row, lining it up
for K5 to S5)

Currently I will have to hit the execute button more than 1 time if Cell J5
remains more than 0 in order to transfer the myCopy to Data.

What I am looking at is to execute the VBA or do a loop until J5 becomes 0
once I hit the execute button.

Thank you.

Sincerely
Stan
xl2003