Enter Data Based on range input

B

bmoore45

New Member
Joined
Jun 19, 2011
Messages
39
Hey guys,

I have cells, one for minimum and one for maximum (eg min(g6)=10, max(h6)=40

minmax.jpg


How can I automatically get a range of data entered into another spreadsheet like this:

data.jpg


Thanks guys,

Ben
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Hi Ben,

Here's one way I suppose. Just change the variables I've marked with 'Change to suit' to meet your specific needs:

Code: 
Option Explicit
Sub Macro3()

    Dim objSourceTab As Object, _
        objDestinTab As Object
    Dim lngLoopCount As Long, _
        lngRowOutput As Long
        
    Set objSourceTab = Sheets("Sheet1") 'Tab name with minimum and maximum limits.  Change to suit.
    Set objDestinTab = Sheets("Sheet2") 'Tab name for output.  Change to suit.
    
    'Ensure there has been a minimum (cell G6) and a maximum (cell H6) entered.
    If Len(objSourceTab.Range("G6")) = 0 Then
        MsgBox "There is no minmum value entered.", vbInformation, "Value List Editor"
        Exit Sub
    ElseIf Len(objSourceTab.Range("H6")) = 0 Then
        MsgBox "There is no maximum value entered.", vbInformation, "Value List Editor"
        Exit Sub
    End If
    
    lngRowOutput = 2
    
    Application.ScreenUpdating = False
    
    For lngLoopCount = objSourceTab.Range("G6").Value To objSourceTab.Range("H6").Value
    
        If lngRowOutput = 0 Then
            lngRowOutput = 2 'Initial row number output. Change to suit.
        Else
            lngRowOutput = lngRowOutput + 1
        End If
    
        objDestinTab.Range("A" & lngRowOutput).Value = lngLoopCount 'Output to Col A. Change to suit
        
    Next lngLoopCount
    
    Application.ScreenUpdating = True

End Sub

HTH

Robert
 
Upvote 0
And here's a more efficient way:

Code: 
Option Explicit
Sub Macro4()

    Dim objSourceTab As Object, _
        objDestinTab As Object
        
    Set objSourceTab = Sheets("Sheet1") 'Tab name with minimum and maximum limits.  Change to suit.
    Set objDestinTab = Sheets("Sheet2") 'Tab name for output.  Change to suit.
    
    'Ensure there has been a minimum (cell G6) and a maximum (cell H6) entered.
    If Len(objSourceTab.Range("G6")) = 0 Then
        MsgBox "There is no minmum value entered.", vbInformation, "Value List Editor"
        Exit Sub
    ElseIf Len(objSourceTab.Range("H6")) = 0 Then
        MsgBox "There is no maximum value entered.", vbInformation, "Value List Editor"
        Exit Sub
    End If
       
    Application.ScreenUpdating = False
        
    'Copy the formula down from A2
    'Note while you can adjust the following formula output to any Column, the minimum Row number is 2.
    With objDestinTab.Range("A2:A" & objSourceTab.Range("H6").Value - objSourceTab.Range("G6").Value + 2)
        .Formula = "=IF(ROW()=2,Sheet1!G6,A1+1)"
        'Converts the above formula range to values.  Comment out if not required.
        .Value = .Value
    End With
    
    Application.ScreenUpdating = True

End Sub

HTH

Robert
 
Upvote 0
Maybe this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Máx</td><td style="text-align: center;;">Min</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">40</td><td style="text-align: center;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">******</td><td style=";">******</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet11</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A2</th><td style="text-align:left">=MAX(<font color="Blue">D2:D61</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=MIN(<font color="Blue">D2:D61</font>)</td></tr></tbody></table></td></tr></table><br /><b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Numbers</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">10</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">11</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">12</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: center;;">37</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: center;;">38</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: center;;">39</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: center;;">40</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style=";"></td><td style="text-align: center;;">*****</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet12</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A2</th><td style="text-align:left">=IF(<font color="Blue">ROW(<font color="Red"></font>)=ROW(<font color="Red">A$2</font>),Sheet11!B2,IF(<font color="Red">A1>=Sheet11!A$2,"",A1+1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A29</th><td style="text-align:left">=IF(<font color="Blue">ROW(<font color="Red"></font>)=ROW(<font color="Red">A$2</font>),Sheet11!B29,IF(<font color="Red">A28>=Sheet11!A$2,"",A28+1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz
 
Upvote 0
Trebor76 said:
And here's a more efficient way:

Code: 
Option Explicit
Sub Macro4()
 
    Dim objSourceTab As Object, _
        objDestinTab As Object
 
    Set objSourceTab = Sheets("Sheet1") 'Tab name with minimum and maximum limits.  Change to suit.
    Set objDestinTab = Sheets("Sheet2") 'Tab name for output.  Change to suit.
 
    'Ensure there has been a minimum (cell G6) and a maximum (cell H6) entered.
    If Len(objSourceTab.Range("G6")) = 0 Then
        MsgBox "There is no minmum value entered.", vbInformation, "Value List Editor"
        Exit Sub
    ElseIf Len(objSourceTab.Range("H6")) = 0 Then
        MsgBox "There is no maximum value entered.", vbInformation, "Value List Editor"
        Exit Sub
    End If
 
    Application.ScreenUpdating = False
 
    'Copy the formula down from A2
    'Note while you can adjust the following formula output to any Column, the minimum Row number is 2.
    With objDestinTab.Range("A2:A" & objSourceTab.Range("H6").Value - objSourceTab.Range("G6").Value + 2)
        .Formula = "=IF(ROW()=2,Sheet1!G6,A1+1)"
        'Converts the above formula range to values.  Comment out if not required.
        .Value = .Value
    End With
 
    Application.ScreenUpdating = True
 
End Sub

HTH

Robert
Click to expand...

Hi Robert,

Thanks I am still new to vba.

I learnt .value=.value which worked like charm in my own vba project.

Biz
 
Upvote 0
Hi Biz,

Nice to hear MrExcel has helped you in some way today.

Regards,

Robert
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Multiple user input value reference
Replies
6
Views
178
Hapakaya
H
A
  • Solved
IF Statement Help (again)
Replies
6
Views
236
dreid1011
dreid1011
W
  • Question Question
Charting range as a vertical bar chart
Replies
1
Views
218
felixstraube
F
J
  • Solved
Calculate sums based on values
Replies
3
Views
298
NateSC
NateSC
T
  • Question Question
Extending a SUM range, based upon a cell value
Replies
5
Views
690
Cubist
Cubist

Forum statistics

Threads
1,224,586
Messages
6,179,710
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top