[VBA] Set particular input cells to specific rows (use of for loop)

[HarrisonChong]

Good day,

As below code, it copies whatever value in A1:C1 to empty row, start from E2:G2 (Credit goes to tygrrboi).
Can anyone help to modify the code, as I tried to modify myself and I have very limited knowledge in VBA, which I couldn't make it myself.

I have 5 input fields from different cell, which are C18, B3, C20, C22 and C24 (Input). I attempt to copy the value from these cells, then arrange in a proper manner as B41:B45 (As output).
Below is the explanation of where the input flow.
Whereas, C18 value goes to B41, B3 value goes to B42, C20 value in B43, C22 value in B44 and C24 value in B45.
However, when the button clicks for 2nd times, then the output will C41:C45 and on so (consider as infinite loop)

Appreciate of anyone who try to help, thank you.

Private Sub CopyAcross_Click()Dim R As Range
Dim rSize As Integer, numCopies As Integer, i As Integer
Dim nextEmptyRow As Long
With ActiveSheet
    Set R = .Range("A1:C1")
    rSize = R.Count
    LastRow = .Cells(.Rows.Count, rSize + 1).End(xlUp).Row
End With
numCopies = 3
For i = 1 To numCopies
    R.Copy R.Offset(LastRow, i * rSize)
Next i
End Sub

 

[L. Howard]

Try this.

Private Sub CopyAcross_Click()

With ActiveSheet
    .Range("B41").Value = Range("C18")
    .Range("B42").Value = Range("B3")
    .Range("B43").Value = Range("C20")
    .Range("B44").Value = Range("C22")
    .Range("B45").Value = Range("C24")
End With

End Sub

I don't understand what this means.

However, when the button clicks for 2nd times, then the output will C41:C45 and on so (consider as infinite loop)

Howard

 

[HarrisonChong]

Hi Howard,

Please see screen-shot below, where as I made input in A1:C1 and I clicked command button 3 times, thus the input appeared in D2:F2.
You see, my function worked properly from A1:C1 to D2:F2. But for "C18 value goes to B41, B3 value goes to B42, C20 value in B43, C22 value in B44 and C24 value in B45".
I have no idea how to work on it, could you help me? Thanks.

 

[L. Howard]

I cannot view your screenshot, it doesn't seem to be enlargeable.

Can you post a link to an example workbook showing the results you want when you click the button three times for "C18 value goes to B41, B3 value goes to B42, C20 value in B43, C22 value in B44 and C24 value in B45".

You can use one of the link utilities to do that, Drop Box or others.
You cannot do attachments here.

Howard

 

[HarrisonChong]

Hello Howard,

Sorry for my late reply.
Here, I upload a sample of the workbook, please have a look. I tried to explain with data sample.
Can you see that, at row "sample here", those data are sorted accordingly from C18, B3, C20, C22, C24. I used different colors of circle to indicate the flow of data.
As the "button 2" is clicked, the data from C18, B3, C20, C22, C24 will flow to column "sample here" and so on. (Consider I clicked twice the button, that is why there are 2 rows with same data.)
I would like to have the "button 2" to flow my data from above to below column continuously, but keep the column's existing data.
(Because I have multiple inputs in dimension calculator, so I have to use a button to convert the I have entered in dimension calculator as column by column. Then I can copy all data that appear in column then paste into report.)

 

[L. Howard]

Does cells C18, C20, C22, have formulas in them to sum the Total and the weights?
Is there a formula in C 24 to return the 5ctn@66x51x64?

Do you enter the data like you show in row 3, by selecting a qty in A, then a Package option in B, then enter a length, width and height in C, D, E? In that order each and every time?

You only show a single row completed, row 3, in the example. I assume there may be several rows filled in and the SAMPLE HERE rows will each show the data for each row filled in.

So if you had all rows from row 3 down to row 10 filled in with shipping data there would be 8 SAMPLE HERE rows.

Do the actual SAMPLE HERE rows start at row 27 or are they actually starting at row B41 or there about as you mention in your earlier post.

If you could use something like Drop Box to post a link to your actual example workbook, it would be helpful.

Howard

 

[HarrisonChong]

Hi,

Yes, C18 and C22 do have formula, while C20 doesn't.
C24 has formula, that used to manipulate the overall data from row 3 to 17 as format below. (If there are 2 input rows made, then C24 data sample as below and so on)

qtypkg@length*width*height,
qtypkg@length*width*height

Yes, I enter the data as showed in row 3, the order is fixed everytime.

Yes, your assumption is correct, there may be several rows being used from row 3 to row 17.

No, since these are just raw data, I wouldn't want it to ship over "SAMPLE HERE". (I need the manipulated data, which are C18, B3, C20, C22, C24) These data are supposed ship over SAMPLE HERE row each time the button is clicked.

No, the actual SAMPLE HERE suppose starting from B41 and so on, due to the limited height of monitor, then I just used row 27 to minimize the length.

Once the C18, B3, C20, C22, C24 have been determined, I would like to have the button, which help me to copy the value of C18, B3, C20, C22, C24 to "SAMPLE HERE" row. When these data are located in SAMPLE HERE, I will clear the input from C18, B3, C20, C22, C24 with a refresh button, then enter new data into it. Which, the button also allowed me to copy the new data (second time of input to row 28), as there are data exists in row 27 from previous input.

I have no rights to upload the actual workbook sample due to some "confidential policy" of the firm, hope you understand, thank you.

FYI, these formula are working perfectly. I just have no idea how to code the function to copy and paste C18, B3, C20, C22, C24 to SAMPLE HERE row and later on.

 

[L. Howard]

Okay, all that info helps.

Howard

 

[L. Howard]

What are the formulas in these cells.

Does cells C18, C20, C22, have formulas in them to sum the Total and the weights?
Is there a formula in C 24 to return the 5ctn@66x51x64?

This will help me better understand how the whole sheet works.

Howard

 

[L. Howard]

I was hoping to get the formulas to fully test my codes.

To run the codes, you will make the selections in column A, B, C, D and last is column E.
There is no button to click, just make the entries (selections) in the order as I stated.

If you skip a selection in one of the first four columns, and make a selection in column E, you will get a warning about blank cells. Read the prompt, and remember after filling the blanks you will need to RE-ENTER THE SELECTION IN COLUMN E! That will run the code after filling the blank/s.

Howard


Copy and paste this first code in the sheet module. The one you show the screen shot in Post #5

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("$A$3:$E$17")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

Dim mbReply As VbMsgBoxResult
Dim aRng As Range
Dim aRow As Long, aRngN As Long

aRow = Target.Row

Set aRng = Range(Cells(aRow, 1), Cells(aRow, 5))
    aRngN = WorksheetFunction.CountA(Range(Cells(aRow, 1), Cells(aRow, 5)))

Select Case Target.Column

    Case 1, 2, 3, 4
        Target.Offset(, 1).Activate
 
    Case 5
        If aRngN <> 5 Then
        
          mbReply = MsgBox("A cell or cells in Row " & aRow & " is missing an entry." _
                      & vbCr & vbCr & "Re-check your entries." _
                      & vbCr & "When all BLANK entries are complete," _
                      & vbCr & vbCr & "Re_Enter column E value.", _
                      vbOK + vbExclamation, "Five Entries Needed!")
        
            If mbReply = vbOK Then
               aRng.SpecialCells(xlCellTypeBlanks).Select
            End If
            
          ElseIf aRngN = 5 Then
            myRng_Arry_Cells
            Target.Offset(1, -4).Activate
        End If
     
End Select

End Sub

Copy and paste this code in a standard module, a module you inserted from the tool bar drop using the Insert > Module in the VB Editor.

Option Explicit

Sub myRng_Arry_Cells()

Dim myRng As Range
Dim rngC As Range
Dim i As Long, r As Long
Dim myArr() As Variant

r = ActiveCell.Row

Set myRng = Range("C18, B" & r & ", C20, C22, C24")

For Each rngC In myRng

    ReDim Preserve myArr(myRng.Cells.Count - 1)
    myArr(i) = rngC
    i = i + 1
Next

With Sheets("Sheet3").Range("C" & Rows.Count).End(xlUp)(2)

   .Resize(columnsize:=myRng.Cells.Count) = myArr  ' to a row
   .Value = .Value

End With

End Sub