Solved: A Loop?

[Carina33]

Hi,

If someone could help me with this problem I would really appreciate it.

I have a seperate area for input by the user. This is basically two cells in which I want the user to be prompted to input two numbers (non-negative). Once the user has done this I want the two numbers to be moved to another location, a table, but the numbers need to be placed in two empty cells. I.e the first time the user inputs two numbers the macro should move them to the first row of the table, the second time the user inputs two numbers they should be copied to the second row of the table etc. Also, once the two numbers for the input area have been validated (non-negative etc) and they have been stored in the correct location in the table, they should be deleted from the input area and the user should be prompted to enter two new numbers.

I have no idea what code to use for this, I am new to vba.

Not sure I am being too clear on what it required, please let me know if I need to describe the problem in more detail.

Many many thanks in advance

Carina

[mdmackillop]

Hi Carina,
Welcome to VBAX
Here's a simple example. If your source and target are not adjoining as shown, let us have the addresses and we can adjust the code to suit
Regards
MD
[vba]
Option Explicit
Sub CopyData()
Dim Source As Range
Dim Tgt As Range
Set Source = Range("A2:B2")
Set Tgt = Cells(Rows.Count, 5).End(xlUp).Offset(1)
Source.Copy Tgt
Source.ClearContents
End Sub

[/vba]

[Norie]

Carina

I don't see any need for a loop.

But it would help if there was more information.

Where will the numbers be input and where will they go?

[Carina33]

Hi MD, thanks so much for the reply.

My target data is in cells A9 and B9 i.e below the source data. I guess I should be able to figure out how to adjust the code but it might take me another 100 years or so... so grateful if you could advice.

Many thanks

Carina

[mdmackillop]

No problem.
[VBA]
Option Explicit
Sub CopyData()
Dim Source As Range
Dim Tgt As Range
Set Source = Range("A9:B9")
Set Tgt = Cells(Rows.Count, 1).End(xlUp).Offset(1)
Source.Copy Tgt
Source.ClearContents
End Sub

[/VBA]

[JimmyTheHand]

Carina,
Norie is right, the target range should be specified as well. Where do the numbers go? Is it in the same sheet or a 2nd one? Or even in another workbook?

Also, in my interpretation, your wish is to have this "moving-the-data-and-clearing-the-input-range" action done automatically, so that no buttonclicking, macrorunning, nor any other manual action would be needed. Right?

Jimmy

[Carina33]

Hi!

Thanks so much for your comments. I will try to describe what needs to happen in a bit more detail hoping this might clarify what code I need, sorry it is so wordy...

Here is what needs to happened step by step:

• The user enters two numbers in a separate input area. Let the input area be in sheet 1, cells A1 and B1. The user should then press a button on sheet 1 and the following should happen:
• These two numbers should be validated so that they are non-negative and are numbers (not letters). If they are not valid then the user should be told to enter two new numbers in cells A1 and B1 on sheet 1.
• If the numbers are ok then the two numbers should be copied to a table consisting of two columns in another sheet so that the number in A1 on sheet 1 is copied to cell A1 on sheet 2 and the second number in B1 on sheet 1 is copied to cell B1 on sheet 2.
• Once the numbers have been copied to the table in sheet 2 a formula in the adjoining columns is calculated i.e. in C1 there is now a value which has been calculated using the number in A1. In cell D1 there is a value that has been calculated using the value in B1. (This is all in sheet 2). Now, at the end of the adjoining table say in C16 and D16 there is a sum total in each of these two cells. If this Sumtotal is larger than a certain value in say E16 and F16 then the numbers entered in sheet 1 and which have been used to calculate the new total in C16 and D16 via the formula, are not valid and the user should be prompted to enter new numbers in sheet 1 cells A1 & B1 and the numbers copied to sheet 2 in A1 and B1 should not be accepted (deleted).
• However, if the total in C16 and D 16 is not greater than the numbers in E16 and F16 respectively, then the numbers entered in sheet 1 should be accepted and they should be stored in sheet 2 in cells A1 and B1. Next, the user should be prompted to enter new numbers in cells A1 and B1 on sheet 1 and the previous numbers, now stored in sheet 2, should be deleted. The user can choose to enter new numbers or exit. If he chooses to enter another two numbers on sheet 1 then these should be validated in the same way as in step 2 above and if they are ok, copied to cells A2 and B2 on sheet 2 and so on. If he chooses to exit then next time he enters numbers and presses the button, these needs to be copied to cells A2 and B2 on sheet 2.

My main problem I think is that I cannot get the programme to ‘remember’ that each time the user enters two numbers on sheet 1 and they meet the requirements (non-negative, a number, totals in sheet 2 not exceeding a certain number) then they should be stored in a unique location in sheet 2 i.e. the third time the user enters numbers they should be stored in A3 and B3.

Is it any clearer? Very grateful for help and/or comments.

Cheers

Carina

[JimmyTheHand]

Yes, it's clearer, thanks. Two questions remains, though, at least for me.

1) What is exactly C16 in Sheet2?
Is it the sum of cells C1 through C15? Or A1 through A15? (I guess D16 is the same, just with cells of column B or D.)

2) What is exactly done with C16 and D16. I can't decide between these options:

• Check C16 against E16 and D16 against F16 after each pair of numbers entered on Sheet1, and if either C16 or D16 are greater than their respective target values, reject only the last pair of numbers.

• Check C16 against E16 and D16 against F16 after each pair of numbers entered on Sheet1, and if either C16 or D16 are greater than their respective target values reject each pair of numbers that have been entered so far.

• Check C16 against E16 and D16 against F16 after 15 pairs of numbers have been entered on Sheet1, and if either C16 or D16 are greater than their respective target values reject all 15 pairs of numbers.

• Something else I haven't thought of.

Jimmy

[mdmackillop]

Try the attached.
[vba]
Option Explicit
Sub CopyData()
Dim Source As Range, cel As Range
Dim Tgt As Range
Dim Chk As Range
'Define ranges
Set Source = Range("A1:B1")
Set Chk = Sheets("Sheet2").Range("E16:F16")
'Check for positive numbers
For Each cel In Source
If cel < 0 Or Not IsNumeric(cel) Then
MsgBox "Enter 2 positive numbers in data cells"
GoTo Exits
End If
Next
'Copy data and write formulae to sheet 2
Set Tgt = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 2)
Source.Copy Tgt
Tgt(1).Offset(, 2).FormulaR1C1 = "=RC1*15"
Tgt(2).Offset(, 2).FormulaR1C1 = "=RC1*RC2*25"
Tgt.Offset(1, 2).FormulaR1C1 = "=SUM(R1C:R[-1]C)"
'Check results against Check Values
If Tgt(1).Offset(1, 2) > Chk(1) Or Tgt(2).Offset(1, 2) > Chk(2) Then
'Delete bad results and totals
Tgt.ClearContents
Tgt.Offset(1, 2).ClearContents
Tgt.Offset(0, 2).FormulaR1C1 = "=SUM(R1C:R[-1]C)"
MsgBox "Check values exceeded. Re-enter 2 positive numbers in data cells"
GoTo Exits
End If
'Clear entered data
Source.ClearContents
Exit Sub
Exits:
Source.ClearContents
End Sub

[/vba]

[Carina33]

Dear Jimmy

Thanks a lot for your comments.

Re 1: you are correct. C16 is the sum of C1 through C15.

Re 2: your first option is correct. Only the last pair entered should be rejected.

Thanks again,

C

[Carina33]

Hi,

Sorry can?t get it to work?

I hope I can explain more clearly this time? the two numbers are input in cells c8 and c9 on sheet 1. After they have been checked to be positive numbers they should be copied to cells b11 and c11 on sheet 2 (c8 is copied to b11 and c9 is copied to c11). There are formulas in cell d11 and cell e11 (in sheet 2) which uses the numbers in b11 and c11 (formula in d11 uses number in b11, formula in e11 uses number is c11). In d26 there is a sum total which is updated when the formula in d11 has been calculated and in e26 there is another sum total which is updated when the formula in e11 has been calculated. The sumtotal in d26 must not be greater than the sumtotal in f26 and the sumtotal in e26 must not be greater than the sumtotal in g26. If either of the sumtotals are greater then in cells f26 and g26 then the numbers entered in c8 and c9 on sheet 1 and which were used to calculate the formula and hence causing the new sumtotal, should be rejected. Only this pair should be rejected. If the sumtotals are ok then the numbers in c8 and c9 should be accepted and stored in cells b11 and c11 on sheet 2. The user should be prompted to enter two new numbers in c8 and c9 on sheet 1 which must be copied and if found to be ok stored in b12 and c12. It must not ?type over? any pairs of numbers previously entered and accepted.

Please comment, help greatly appreciated.

C

[mdmackillop]

You'll need to add your own formulae into cells D11:E25

[Carina33]

Dear MD

Thanks for this. I still have a problem though.. because with this procedure the two numbers entered on sheet 1 is pasted after the very last row that has a value on sheet 2. Please, I have other tables and things below cells d26 and e26 on sheet 2. How can I get the numbers entered in cells c8 and c9 on sheet 1 to be entered in cells b11 and c11 on sheet 2 and the next time the user enters two numbers on sheet 1 they should be in b12 and c12, next time in b13 and c13 etc)? All the others things like the validation against cells F26 and G26 remain the same.

Thanks again in advance!

C

[mdmackillop]

Change to this line and the data will be inserted in the first available row above row 26. The Data1 & Data2 headers (or similar) are needed to set the upper limit.
[VBA]
Set Tgt = Sheets("Sheet2").Cells(26, 2).End(xlUp).Offset(1).Resize(, 2)
[/VBA]

[Carina33]

Hi MD

Thanks a ton for your reply. Now this part finally works! Not that I have clue way because I don' really understand the code, especially the following line

Set Tgt = Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Offset(1).Resize(, 2)

If someone could please explain what the part starting from Cells mean I would be really grateful. I do not understand how excel finds the correct cells to paste the values in And how excel knows to paste the next pair of numbers directly under the previous pair...

Thanks again for all your help, really appeciate it.

Cheers

C

[JimmyTheHand]

Let's take it apart.

Cells are referenced by: Cells(RowIndex, ColumnIndex)
Rows.Count counts rows in the sheet. So, Cells(Rows.Count, 2) places the reference point into that individual cell that is the intersection of the last row and column B.

Assuming that the last reference point was an empty cell, .End takes the reference point to the 1st used cell in the given direction. So, in the example of .End(xlUp), it takes you upwards.

Cells(Rows.Count, 2).End(xlUp) starts from the location that Cells(Rows.Count, 2) determines, and looks for the first used cell, upwards, in column B. The result is essentially the last used cell in column B.

Offset(RowIndex, ColumnIndex) moves the reference point down by RowIndex, and to the right by ColumnIndex.
RowIndex and ColumnIndex can both be negative, in which case reference point is moved upwards and to the left, respectively. If any of them is omitted then it's considered zero.

Offset(1) takes the reference point down by one cell, as ColumnIndex is omitted.

So, Cells(Rows.Count, 2).End(xlUp).Offset(1) takes you from the last used cell in column B down by one cell, which can be considered as the first empty cell in column B.

So, the reference point is now the first empty cell in column B.
Resize(RowIndex, ColumnIndex) resizes the reference point, which was only one cell up till now, to a larger area, i.e. a compount of cells, in a rectangular shape. If any of the indices is omitted, it is considered unchanged.
Resize(, 2) resizes the current reference point to a compound of
two cells in the same row, because RowIndex was not specified. In other words, Resize(, 2) expands the current reference point so that it now covers the current cell plus to one next to it on the right.

Thus, Cells(Rows.Count, 2).End(xlUp).Offset(1).Resize(, 2) is a two-cell range, the first empty cell at the end of column B, and the cell right next to it, together.

And this range is located on Sheet2, and has been named as Tgt.

Jimmy

[Norie]

Jimmy

Rows.Count doesn't count the used rows, it counts all the rows in the sheet.

Try this with a blank workbook.

MsgBox Rows.Count

[JimmyTheHand]

Jimmy

Rows.Count doesn't count the used rows, it counts all the rows in the sheet.

Try this with a blank workbook.

MsgBox Rows.Count

Me duh!!!
Good point. I updated my previous post.

[mdmackillop]

Thanks Jimmy.

[Carina33]

Thanks a lot for taking the time to explain this! It is great to learn.