AutoFill range based on cell value

[BlokeMan]

Hi,

Drop down list value is in D2, based on that value it will autofill range B2:B31. If the value selected in D2 is 24 then Autofill range B2:B31 to 24,48,72 to 720, same as when selected 30 then 30,60,90 to 900. I would like to do this in macro.

Thanks

 

[Peter_SSs]

Give this a try in a copy of your workbook. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("D2")) Is Nothing Then
    Application.EnableEvents = False
    With Range("B2:B31")
      If IsNumeric(Range("D2").Value) And Range("D2").Value <> "" Then
        Range("B2:B31").Value = Evaluate("IF(1,(row(" & Range("B2:B31").Address & ")-1)*" & Range("D2").Value & ")")
      Else
        Range("B2:B31").ClearContents
      End If
    End With
    Application.EnableEvents = True
  End If
End Sub

 

[BlokeMan]

Thanks Peter, works great, have a nice weekends.

 

[Peter_SSs]

Thanks Peter, works great, have a nice weekends.

You're welcome. Thanks for the follow-up.

 

[BlokeMan]

Hi Peter,

I would like to add a check that if I enter a value in range I2:N14 the values in B2:B31 will adjust, example if I enter 1 the values will be 25,49,73 and so on (based on the value 24 in D2) and if I enter another number 45 then it will be 25,50,74 and so on.

Thanks again

 

[Peter_SSs]

I don't understand this new requirement. You will have to set it out in more detail, including exactly what values are put in what cells as well as the results that would follow.

BTW, why do you want this via a macro? Why not just place appropriate formulas that would automatically adjust in B2:B31?

 

[BlokeMan]

Thanks Peter for your reply, Actually the numbers in column B represents the number of shippers in a pallet, we receive a roll of label numbering from 1 to 800 and we stick that label in every shipper (B2 is pallet #1 , B3 pallet #2 and so on) and D2 represent how many shippers in a pallet, the number of shipper in the pallet does not change but if you sample or reject the labels then the last label on that pallet will change, this is the way to track the last label on that pallet when you sample/reject labels. I hope this explain everything. If you have any solution or other way to do this then I'm all ears. Thanks again.

 

[Peter_SSs]

I hope this explain everything.

Sorry, it doesn't.

What I need to know is what you are putting, & where, on your worksheet and what the corresponding result(s) should be, and where.

I can't see what is, or may be, in the 78 cells of I2:N14 nor what effect any value in that range would have on the desired results.

As an example, you said "if I enter another number 45 then it will be 25,50,74 and so on." However, you didn't say what cell the 45 is in nor how 45 in that cell would result in 25, 50, 74, ...

 

[BlokeMan]

Example:

I'm going to use 24:

B2=24 (label #1 to #24 )
B3=48 (label #25 to #48 )
B4=72 (label #49 to #72 )
B5=96 (label #73 to #96 )

All sample/reject labels in range I2:N14

If I'm going to reject label #1 then it will affect B2 to B31:

B2=25 (label #2 to #25 ) reject #1
B3=49 (label #2 6 to #49 )
B4=73 (label #50 to #73 )
B5=97 (label #74 to #97 )

If I'm going to reject Label #1 and #50 then:

B2=25 (label #2 to #25 ) reject #1
B3=49 (label #2 6 to #49 )
B4=74 (label #51 to #74 ) reject #50
B5=98 (label #75 to #98 )

This will automatically adjusted when rejecting numbers when entering all rejected numbers in range I2:N14.
Hope this help

 

[Peter_SSs]

OK thanks. I think I am slowly getting the idea.
Try this Worksheet_Change code. Previous code will need to be removed.

If this seems to be doing the correct thing then please check that cells B30 and B31 show the values that you expect when
B2=24
I2:N14 contains all values from 700 to 720 inclusive & no other numbers

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim aFullList As Variant, aRejects As Variant, vReject As Variant
  Dim aResults(1 To 30, 1 To 1) As Variant
  Dim i As Long, lSize As Long, k As Long
  
  If Not Intersect(Target, Union(Range("D2"), Range("I2:N14"))) Is Nothing Then
    lSize = Range("D2").Value
    If lSize > 0 Then
      aFullList = Application.Transpose(Evaluate("row(1:" & 30 * lSize & ")"))
      aRejects = Range("I2:N14").Value
      For Each vReject In aRejects
        If Not IsEmpty(vReject) Then
          aFullList(vReject) = "x"
        End If
      Next vReject
      aFullList = Filter(aFullList, "x", False)
      For i = lSize - 1 To UBound(aFullList) Step lSize
        k = k + 1
        aResults(k, 1) = aFullList(i)
      Next i
      If aResults(k, 1) <> aFullList(UBound(aFullList)) Then aResults(k + 1, 1) = aFullList(UBound(aFullList))
    End If
    Application.EnableEvents = False
    Range("B2:B31").Value = aResults
    Application.EnableEvents = True
  End If
End Sub