Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > I need smaller code.
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default I need smaller code.
Hi,

I am trying to put some code on a command button that will
move data depending on what value is chosen from in a list
box. Problem is it's alot of data and I've only written
one case and it's really long. I need 12 cases in all and
that would make it too long and hard to create. I'm pretty
new to visual basic and I've heard of looping and I don't
know if I fully understand it yet. Could anyone help me?
Basically what I need is to create the following case 11
more times. The destination cells columns rows and ranges
will be the same but the source rows should increase by 40
rows. Everything else stays the same. Here the first case
in my code....Can anyone help me with what my code should
look like?

Option Explicit
Private Sub Up1_Click()
Select Case LB1.ListIndex
Case 0 'First item
Worksheets("MRep1").Range("P28:U28").Value =
Worksheets("Evaluation").Range("B17:G17").Value
Worksheets("MRep1").Range("P27:U27").Value =
Worksheets("Evaluation").Range("B25:G25").Value
Worksheets("MRep1").Range("P29:U29").Value =
Worksheets("Evaluation").Range("B26:G26").Value
Worksheets("MRep1").Range("D28:I28").Value =
Worksheets("Evaluation").Range("B18:G18").Value
Worksheets("MRep1").Range("D27:I27").Value =
Worksheets("Evaluation").Range("B23:G23").Value
Worksheets("MRep1").Range("D29:I29").Value =
Worksheets("Evaluation").Range("B24:G24").Value
Worksheets("MRep1").Range("J54:O54").Value =
Worksheets("Evaluation").Range("B7:G7").Value
Worksheets("MRep1").Range("J55:O55").Value =
Worksheets("Evaluation").Range("B12:G12").Value
Worksheets("MRep1").Range("J56:O56").Value =
Worksheets("Evaluation").Range("B27:G27").Value
Worksheets("MRep1").Range("D55").Value =
Worksheets("Evaluation").Range("G13").Value
Worksheets("MRep1").Range("E55").Value =
Worksheets("Evaluation").Range("G8").Value
Worksheets("MRep1").Range("S54").Value =
Worksheets("Evaluation").Range("I5").Value
Worksheets("MRep1").Range("T54").Value =
Worksheets("Evaluation").Range("N5").Value
Worksheets("MRep1").Range("U54").Value =
Worksheets("Evaluation").Range("S5").Value
Worksheets("MRep1").Range("V54").Value =
Worksheets("Evaluation").Range("I10").Value
Worksheets("MRep1").Range("W54").Value =
Worksheets("Evaluation").Range("N10").Value
Worksheets("MRep1").Range("X54").Value =
Worksheets("Evaluation").Range("S10").Value
Worksheets("MRep1").Range("S55").Value =
Worksheets("Evaluation").Range("I7").Value
Worksheets("MRep1").Range("T55").Value =
Worksheets("Evaluation").Range("N7").Value
Worksheets("MRep1").Range("U55").Value =
Worksheets("Evaluation").Range("S7").Value
Worksheets("MRep1").Range("V55").Value =
Worksheets("Evaluation").Range("I12").Value
Worksheets("MRep1").Range("W55").Value =
Worksheets("Evaluation").Range("N12").Value
Worksheets("MRep1").Range("X55").Value =
Worksheets("Evaluation").Range("S12").Value
Worksheets("MRep1").Range("S56").Value =
Worksheets("Evaluation").Range("I8").Value
Worksheets("MRep1").Range("T56").Value =
Worksheets("Evaluation").Range("N8").Value
Worksheets("MRep1").Range("U56").Value =
Worksheets("Evaluation").Range("S8").Value
Worksheets("MRep1").Range("V56").Value =
Worksheets("Evaluation").Range("I13").Value
Worksheets("MRep1").Range("W56").Value =
Worksheets("Evaluation").Range("N13").Value
Worksheets("MRep1").Range("X56").Value =
Worksheets("Evaluation").Range("S13").Value
Worksheets("MRep2").Range("D52:I52").Value =
Worksheets("Evaluation").Range("B18:G18").Value
Worksheets("MRep2").Range("D53:I53").Value =
Worksheets("Evaluation").Range("B28:G28").Value
Worksheets("MRep2").Range("D54:I54").Value =
Worksheets("Evaluation").Range("B29:G29").Value
Worksheets("MRep2").Range("P52:U52").Value =
Worksheets("Evaluation").Range("B30:G30").Value
Worksheets("MRep2").Range("P53:U53").Value =
Worksheets("Evaluation").Range("B31:G31").Value
Worksheets("MRep2").Range("F28").Value =
Worksheets("Evaluation").Range("B33").Value
Worksheets("MRep2").Range("G28").Value =
Worksheets("Evaluation").Range("B34").Value
Worksheets("MRep2").Range("F29").Value =
Worksheets("Evaluation").Range("B35").Value
Worksheets("MRep2").Range("G29").Value =
Worksheets("Evaluation").Range("B36").Value
Worksheets("MRep2").Range("R26").Value =
Worksheets("Evaluation").Range("F33").Value
Worksheets("MRep2").Range("R27").Value =
Worksheets("Evaluation").Range("F34").Value
Worksheets("MRep2").Range("R28").Value =
Worksheets("Evaluation").Range("F35").Value
Worksheets("MRep2").Range("R29").Value =
Worksheets("Evaluation").Range("F36").Value
Worksheets("MRep3").Range("B11").Value =
Worksheets("Evaluation").Range("I10").Value
Worksheets("MRep3").Range("B19").Value =
Worksheets("Evaluation").Range("N10").Value
Worksheets("MRep3").Range("B27").Value =
Worksheets("Evaluation").Range("S10").Value
Worksheets("MRep3").Range("F11").Value =
Worksheets("Evaluation").Range("J14").Value
Worksheets("MRep3").Range("F19").Value =
Worksheets("Evaluation").Range("O14").Value
Worksheets("MRep3").Range("F27").Value =
Worksheets("Evaluation").Range("T14").Value
Worksheets("MRep3").Range("H11").Value =
Worksheets("Evaluation").Range("H16").Value
Worksheets("MRep3").Range("H19").Value =
Worksheets("Evaluation").Range("M16").Value
Worksheets("MRep3").Range("H27").Value =
Worksheets("Evaluation").Range("R16").Value
Worksheets("MRep3").Range("M11").Value =
Worksheets("Evaluation").Range("H21").Value
Worksheets("MRep3").Range("M19").Value =
Worksheets("Evaluation").Range("M21").Value
Worksheets("MRep3").Range("M27").Value =
Worksheets("Evaluation").Range("R21").Value
Worksheets("MRep3").Range("B37").Value =
Worksheets("Evaluation").Range("I26").Value
Worksheets("MRep3").Range("F37").Value =
Worksheets("Evaluation").Range("J30").Value
Worksheets("MRep3").Range("H37").Value =
Worksheets("Evaluation").Range("H32").Value
Worksheets("MRep3").Range("M37").Value =
Worksheets("Evaluation").Range("H37").Value
Worksheets("MRep3").Range("F46").Value =
Worksheets("Evaluation").Range("O30").Value
Worksheets("MRep3").Range("H46").Value =
Worksheets("Evaluation").Range("M32").Value
Worksheets("MRep3").Range("M46").Value =
Worksheets("Evaluation").Range("M37").Value
Worksheets("MRep3").Range("B15:E15").Value =
Worksheets("Evaluation").Range("I12:L12").Value
Worksheets("MRep3").Range("B16:E16").Value =
Worksheets("Evaluation").Range("I13:L13").Value
Worksheets("MRep3").Range("B23:E23").Value =
Worksheets("Evaluation").Range("N12:Q12").Value
Worksheets("MRep3").Range("B24:E24").Value =
Worksheets("Evaluation").Range("N13:Q13").Value
Worksheets("MRep3").Range("B31:E31").Value =
Worksheets("Evaluation").Range("S12:V12").Value
Worksheets("MRep3").Range("B32:E32").Value =
Worksheets("Evaluation").Range("S13:V13").Value
Worksheets("MRep3").Range("B41:E41").Value =
Worksheets("Evaluation").Range("I28:L28").Value
Worksheets("MRep3").Range("B49:E49").Value =
Worksheets("Evaluation").Range("N28:Q28").Value
Worksheets("MRep3").Range("B50:E50").Value =
Worksheets("Evaluation").Range("N29:Q29").Value
Worksheets("MRep3").Range("A15").Value =
Worksheets("Evaluation").Range("G25").Value
Worksheets("MRep3").Range("A23").Value =
Worksheets("Evaluation").Range("G25").Value
Worksheets("MRep3").Range("A31").Value =
Worksheets("Evaluation").Range("G25").Value
Worksheets("MRep3").Range("A41").Value =
Worksheets("Evaluation").Range("I29").Value
Worksheets("MRep4").Range("A9").Value =
Worksheets("Evaluation").Range("W5").Value
Worksheets("MRep4").Range("A16").Value =
Worksheets("Evaluation").Range("W11").Value
Worksheets("MRep4").Range("A23").Value =
Worksheets("Evaluation").Range("W17").Value
Worksheets("MRep4").Range("A30").Value =
Worksheets("Evaluation").Range("W23").Value
Worksheets("MRep4").Range("A37").Value =
Worksheets("Evaluation").Range("W29").Value
End Select
End Sub
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default I need smaller code.
You can use this as a guide:

Option Explicit
Private Sub Up1_Click()
Select Case LB1.ListIndex
Case 0 'First item
Call WriteValues(LB1.ListIndex)
End Select
End Sub

Public Function WriteValues(TheCase As Integer)
Dim ls_RangeString As String
Dim ll_RowNumber As Long

ll_RowNumber = 28 + (TheCase * 40)
ls_RangeString = "P" + CStr(ll_RowNumber) + ":U" + CStr(ll_RowNumber)
Worksheets("MRep1").Range(ls_RangeString).Value =
Worksheets("Evaluation").Range("B17:G17").Value
End Function

Just add the extra case statements to the Up1_Click to accomidate, then do
the following in WriteValues for each line:
change the "28" to the row number
adjust the "ls_RangeString = ..." line so that it conforms with
the current line you're writing from
add ls_RangeString as your range in the line

This will at least cut down the amount of coding you'll have to enter a
little bit.

Tom S.

"Jennifer Glass" wrote in message
...
Hi,

I am trying to put some code on a command button that will
move data depending on what value is chosen from in a list
box. Problem is it's alot of data and I've only written
one case and it's really long. I need 12 cases in all and
that would make it too long and hard to create. I'm pretty
new to visual basic and I've heard of looping and I don't
know if I fully understand it yet. Could anyone help me?
Basically what I need is to create the following case 11
more times. The destination cells columns rows and ranges
will be the same but the source rows should increase by 40
rows. Everything else stays the same. Here the first case
in my code....Can anyone help me with what my code should
look like?

Option Explicit
Private Sub Up1_Click()
Select Case LB1.ListIndex
Case 0 'First item
Worksheets("MRep1").Range("P28:U28").Value =
Worksheets("Evaluation").Range("B17:G17").Value
Worksheets("MRep1").Range("P27:U27").Value =
Worksheets("Evaluation").Range("B25:G25").Value
Worksheets("MRep1").Range("P29:U29").Value =
Worksheets("Evaluation").Range("B26:G26").Value
Worksheets("MRep1").Range("D28:I28").Value =
Worksheets("Evaluation").Range("B18:G18").Value
Worksheets("MRep1").Range("D27:I27").Value =
Worksheets("Evaluation").Range("B23:G23").Value
Worksheets("MRep1").Range("D29:I29").Value =
Worksheets("Evaluation").Range("B24:G24").Value
Worksheets("MRep1").Range("J54:O54").Value =
Worksheets("Evaluation").Range("B7:G7").Value
Worksheets("MRep1").Range("J55:O55").Value =
Worksheets("Evaluation").Range("B12:G12").Value
Worksheets("MRep1").Range("J56:O56").Value =
Worksheets("Evaluation").Range("B27:G27").Value
Worksheets("MRep1").Range("D55").Value =
Worksheets("Evaluation").Range("G13").Value
Worksheets("MRep1").Range("E55").Value =
Worksheets("Evaluation").Range("G8").Value
Worksheets("MRep1").Range("S54").Value =
Worksheets("Evaluation").Range("I5").Value
Worksheets("MRep1").Range("T54").Value =
Worksheets("Evaluation").Range("N5").Value
Worksheets("MRep1").Range("U54").Value =
Worksheets("Evaluation").Range("S5").Value
Worksheets("MRep1").Range("V54").Value =
Worksheets("Evaluation").Range("I10").Value
Worksheets("MRep1").Range("W54").Value =
Worksheets("Evaluation").Range("N10").Value
Worksheets("MRep1").Range("X54").Value =
Worksheets("Evaluation").Range("S10").Value
Worksheets("MRep1").Range("S55").Value =
Worksheets("Evaluation").Range("I7").Value
Worksheets("MRep1").Range("T55").Value =
Worksheets("Evaluation").Range("N7").Value
Worksheets("MRep1").Range("U55").Value =
Worksheets("Evaluation").Range("S7").Value
Worksheets("MRep1").Range("V55").Value =
Worksheets("Evaluation").Range("I12").Value
Worksheets("MRep1").Range("W55").Value =
Worksheets("Evaluation").Range("N12").Value
Worksheets("MRep1").Range("X55").Value =
Worksheets("Evaluation").Range("S12").Value
Worksheets("MRep1").Range("S56").Value =
Worksheets("Evaluation").Range("I8").Value
Worksheets("MRep1").Range("T56").Value =
Worksheets("Evaluation").Range("N8").Value
Worksheets("MRep1").Range("U56").Value =
Worksheets("Evaluation").Range("S8").Value
Worksheets("MRep1").Range("V56").Value =
Worksheets("Evaluation").Range("I13").Value
Worksheets("MRep1").Range("W56").Value =
Worksheets("Evaluation").Range("N13").Value
Worksheets("MRep1").Range("X56").Value =
Worksheets("Evaluation").Range("S13").Value
Worksheets("MRep2").Range("D52:I52").Value =
Worksheets("Evaluation").Range("B18:G18").Value
Worksheets("MRep2").Range("D53:I53").Value =
Worksheets("Evaluation").Range("B28:G28").Value
Worksheets("MRep2").Range("D54:I54").Value =
Worksheets("Evaluation").Range("B29:G29").Value
Worksheets("MRep2").Range("P52:U52").Value =
Worksheets("Evaluation").Range("B30:G30").Value
Worksheets("MRep2").Range("P53:U53").Value =
Worksheets("Evaluation").Range("B31:G31").Value
Worksheets("MRep2").Range("F28").Value =
Worksheets("Evaluation").Range("B33").Value
Worksheets("MRep2").Range("G28").Value =
Worksheets("Evaluation").Range("B34").Value
Worksheets("MRep2").Range("F29").Value =
Worksheets("Evaluation").Range("B35").Value
Worksheets("MRep2").Range("G29").Value =
Worksheets("Evaluation").Range("B36").Value
Worksheets("MRep2").Range("R26").Value =
Worksheets("Evaluation").Range("F33").Value
Worksheets("MRep2").Range("R27").Value =
Worksheets("Evaluation").Range("F34").Value
Worksheets("MRep2").Range("R28").Value =
Worksheets("Evaluation").Range("F35").Value
Worksheets("MRep2").Range("R29").Value =
Worksheets("Evaluation").Range("F36").Value
Worksheets("MRep3").Range("B11").Value =
Worksheets("Evaluation").Range("I10").Value
Worksheets("MRep3").Range("B19").Value =
Worksheets("Evaluation").Range("N10").Value
Worksheets("MRep3").Range("B27").Value =
Worksheets("Evaluation").Range("S10").Value
Worksheets("MRep3").Range("F11").Value =
Worksheets("Evaluation").Range("J14").Value
Worksheets("MRep3").Range("F19").Value =
Worksheets("Evaluation").Range("O14").Value
Worksheets("MRep3").Range("F27").Value =
Worksheets("Evaluation").Range("T14").Value
Worksheets("MRep3").Range("H11").Value =
Worksheets("Evaluation").Range("H16").Value
Worksheets("MRep3").Range("H19").Value =
Worksheets("Evaluation").Range("M16").Value
Worksheets("MRep3").Range("H27").Value =
Worksheets("Evaluation").Range("R16").Value
Worksheets("MRep3").Range("M11").Value =
Worksheets("Evaluation").Range("H21").Value
Worksheets("MRep3").Range("M19").Value =
Worksheets("Evaluation").Range("M21").Value
Worksheets("MRep3").Range("M27").Value =
Worksheets("Evaluation").Range("R21").Value
Worksheets("MRep3").Range("B37").Value =
Worksheets("Evaluation").Range("I26").Value
Worksheets("MRep3").Range("F37").Value =
Worksheets("Evaluation").Range("J30").Value
Worksheets("MRep3").Range("H37").Value =
Worksheets("Evaluation").Range("H32").Value
Worksheets("MRep3").Range("M37").Value =
Worksheets("Evaluation").Range("H37").Value
Worksheets("MRep3").Range("F46").Value =
Worksheets("Evaluation").Range("O30").Value
Worksheets("MRep3").Range("H46").Value =
Worksheets("Evaluation").Range("M32").Value
Worksheets("MRep3").Range("M46").Value =
Worksheets("Evaluation").Range("M37").Value
Worksheets("MRep3").Range("B15:E15").Value =
Worksheets("Evaluation").Range("I12:L12").Value
Worksheets("MRep3").Range("B16:E16").Value =
Worksheets("Evaluation").Range("I13:L13").Value
Worksheets("MRep3").Range("B23:E23").Value =
Worksheets("Evaluation").Range("N12:Q12").Value
Worksheets("MRep3").Range("B24:E24").Value =
Worksheets("Evaluation").Range("N13:Q13").Value
Worksheets("MRep3").Range("B31:E31").Value =
Worksheets("Evaluation").Range("S12:V12").Value
Worksheets("MRep3").Range("B32:E32").Value =
Worksheets("Evaluation").Range("S13:V13").Value
Worksheets("MRep3").Range("B41:E41").Value =
Worksheets("Evaluation").Range("I28:L28").Value
Worksheets("MRep3").Range("B49:E49").Value =
Worksheets("Evaluation").Range("N28:Q28").Value
Worksheets("MRep3").Range("B50:E50").Value =
Worksheets("Evaluation").Range("N29:Q29").Value
Worksheets("MRep3").Range("A15").Value =
Worksheets("Evaluation").Range("G25").Value
Worksheets("MRep3").Range("A23").Value =
Worksheets("Evaluation").Range("G25").Value
Worksheets("MRep3").Range("A31").Value =
Worksheets("Evaluation").Range("G25").Value
Worksheets("MRep3").Range("A41").Value =
Worksheets("Evaluation").Range("I29").Value
Worksheets("MRep4").Range("A9").Value =
Worksheets("Evaluation").Range("W5").Value
Worksheets("MRep4").Range("A16").Value =
Worksheets("Evaluation").Range("W11").Value
Worksheets("MRep4").Range("A23").Value =
Worksheets("Evaluation").Range("W17").Value
Worksheets("MRep4").Range("A30").Value =
Worksheets("Evaluation").Range("W23").Value
Worksheets("MRep4").Range("A37").Value =
Worksheets("Evaluation").Range("W29").Value
End Select
End Sub


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default I need smaller code.
Thank You!

-----Original Message-----
You can use this as a guide:

Option Explicit
Private Sub Up1_Click()
Select Case LB1.ListIndex
Case 0 'First item
Call WriteValues(LB1.ListIndex)
End Select
End Sub

Public Function WriteValues(TheCase As Integer)
Dim ls_RangeString As String
Dim ll_RowNumber As Long

ll_RowNumber = 28 + (TheCase * 40)
ls_RangeString = "P" + CStr(ll_RowNumber) + ":U" +
CStr(ll_RowNumber)
Worksheets("MRep1").Range(ls_RangeString).Value =
Worksheets("Evaluation").Range("B17:G17").Value
End Function

Just add the extra case statements to the Up1_Click to
accomidate, then do
the following in WriteValues for each line:
change the "28" to the row number
adjust the "ls_RangeString = ..." line so
that it conforms with
the current line you're writing from
add ls_RangeString as your range in the line

This will at least cut down the amount of coding you'll
have to enter a
little bit.

Tom S.

"Jennifer Glass"
wrote in message
...
Hi,

I am trying to put some code on a command button that
will
move data depending on what value is chosen from in a
list
box. Problem is it's alot of data and I've only written
one case and it's really long. I need 12 cases in all
and
that would make it too long and hard to create. I'm
pretty
new to visual basic and I've heard of looping and I
don't
know if I fully understand it yet. Could anyone help me?
Basically what I need is to create the following case 11
more times. The destination cells columns rows and
ranges
will be the same but the source rows should increase by
40
rows. Everything else stays the same. Here the first
case
in my code....Can anyone help me with what my code
should
look like?

Option Explicit
Private Sub Up1_Click()
Select Case LB1.ListIndex
Case 0 'First item
Worksheets("MRep1").Range("P28:U28").Value =
Worksheets("Evaluation").Range("B17:G17").Value
Worksheets("MRep1").Range("P27:U27").Value =
Worksheets("Evaluation").Range("B25:G25").Value
Worksheets("MRep1").Range("P29:U29").Value =
Worksheets("Evaluation").Range("B26:G26").Value
Worksheets("MRep1").Range("D28:I28").Value =
Worksheets("Evaluation").Range("B18:G18").Value
Worksheets("MRep1").Range("D27:I27").Value =
Worksheets("Evaluation").Range("B23:G23").Value
Worksheets("MRep1").Range("D29:I29").Value =
Worksheets("Evaluation").Range("B24:G24").Value
Worksheets("MRep1").Range("J54:O54").Value =
Worksheets("Evaluation").Range("B7:G7").Value
Worksheets("MRep1").Range("J55:O55").Value =
Worksheets("Evaluation").Range("B12:G12").Value
Worksheets("MRep1").Range("J56:O56").Value =
Worksheets("Evaluation").Range("B27:G27").Value
Worksheets("MRep1").Range("D55").Value =
Worksheets("Evaluation").Range("G13").Value
Worksheets("MRep1").Range("E55").Value =
Worksheets("Evaluation").Range("G8").Value
Worksheets("MRep1").Range("S54").Value =
Worksheets("Evaluation").Range("I5").Value
Worksheets("MRep1").Range("T54").Value =
Worksheets("Evaluation").Range("N5").Value
Worksheets("MRep1").Range("U54").Value =
Worksheets("Evaluation").Range("S5").Value
Worksheets("MRep1").Range("V54").Value =
Worksheets("Evaluation").Range("I10").Value
Worksheets("MRep1").Range("W54").Value =
Worksheets("Evaluation").Range("N10").Value
Worksheets("MRep1").Range("X54").Value =
Worksheets("Evaluation").Range("S10").Value
Worksheets("MRep1").Range("S55").Value =
Worksheets("Evaluation").Range("I7").Value
Worksheets("MRep1").Range("T55").Value =
Worksheets("Evaluation").Range("N7").Value
Worksheets("MRep1").Range("U55").Value =
Worksheets("Evaluation").Range("S7").Value
Worksheets("MRep1").Range("V55").Value =
Worksheets("Evaluation").Range("I12").Value
Worksheets("MRep1").Range("W55").Value =
Worksheets("Evaluation").Range("N12").Value
Worksheets("MRep1").Range("X55").Value =
Worksheets("Evaluation").Range("S12").Value
Worksheets("MRep1").Range("S56").Value =
Worksheets("Evaluation").Range("I8").Value
Worksheets("MRep1").Range("T56").Value =
Worksheets("Evaluation").Range("N8").Value
Worksheets("MRep1").Range("U56").Value =
Worksheets("Evaluation").Range("S8").Value
Worksheets("MRep1").Range("V56").Value =
Worksheets("Evaluation").Range("I13").Value
Worksheets("MRep1").Range("W56").Value =
Worksheets("Evaluation").Range("N13").Value
Worksheets("MRep1").Range("X56").Value =
Worksheets("Evaluation").Range("S13").Value
Worksheets("MRep2").Range("D52:I52").Value =
Worksheets("Evaluation").Range("B18:G18").Value
Worksheets("MRep2").Range("D53:I53").Value =
Worksheets("Evaluation").Range("B28:G28").Value
Worksheets("MRep2").Range("D54:I54").Value =
Worksheets("Evaluation").Range("B29:G29").Value
Worksheets("MRep2").Range("P52:U52").Value =
Worksheets("Evaluation").Range("B30:G30").Value
Worksheets("MRep2").Range("P53:U53").Value =
Worksheets("Evaluation").Range("B31:G31").Value
Worksheets("MRep2").Range("F28").Value =
Worksheets("Evaluation").Range("B33").Value
Worksheets("MRep2").Range("G28").Value =
Worksheets("Evaluation").Range("B34").Value
Worksheets("MRep2").Range("F29").Value =
Worksheets("Evaluation").Range("B35").Value
Worksheets("MRep2").Range("G29").Value =
Worksheets("Evaluation").Range("B36").Value
Worksheets("MRep2").Range("R26").Value =
Worksheets("Evaluation").Range("F33").Value
Worksheets("MRep2").Range("R27").Value =
Worksheets("Evaluation").Range("F34").Value
Worksheets("MRep2").Range("R28").Value =
Worksheets("Evaluation").Range("F35").Value
Worksheets("MRep2").Range("R29").Value =
Worksheets("Evaluation").Range("F36").Value
Worksheets("MRep3").Range("B11").Value =
Worksheets("Evaluation").Range("I10").Value
Worksheets("MRep3").Range("B19").Value =
Worksheets("Evaluation").Range("N10").Value
Worksheets("MRep3").Range("B27").Value =
Worksheets("Evaluation").Range("S10").Value
Worksheets("MRep3").Range("F11").Value =
Worksheets("Evaluation").Range("J14").Value
Worksheets("MRep3").Range("F19").Value =
Worksheets("Evaluation").Range("O14").Value
Worksheets("MRep3").Range("F27").Value =
Worksheets("Evaluation").Range("T14").Value
Worksheets("MRep3").Range("H11").Value =
Worksheets("Evaluation").Range("H16").Value
Worksheets("MRep3").Range("H19").Value =
Worksheets("Evaluation").Range("M16").Value
Worksheets("MRep3").Range("H27").Value =
Worksheets("Evaluation").Range("R16").Value
Worksheets("MRep3").Range("M11").Value =
Worksheets("Evaluation").Range("H21").Value
Worksheets("MRep3").Range("M19").Value =
Worksheets("Evaluation").Range("M21").Value
Worksheets("MRep3").Range("M27").Value =
Worksheets("Evaluation").Range("R21").Value
Worksheets("MRep3").Range("B37").Value =
Worksheets("Evaluation").Range("I26").Value
Worksheets("MRep3").Range("F37").Value =
Worksheets("Evaluation").Range("J30").Value
Worksheets("MRep3").Range("H37").Value =
Worksheets("Evaluation").Range("H32").Value
Worksheets("MRep3").Range("M37").Value =
Worksheets("Evaluation").Range("H37").Value
Worksheets("MRep3").Range("F46").Value =
Worksheets("Evaluation").Range("O30").Value
Worksheets("MRep3").Range("H46").Value =
Worksheets("Evaluation").Range("M32").Value
Worksheets("MRep3").Range("M46").Value =
Worksheets("Evaluation").Range("M37").Value
Worksheets("MRep3").Range("B15:E15").Value =
Worksheets("Evaluation").Range("I12:L12").Value
Worksheets("MRep3").Range("B16:E16").Value =
Worksheets("Evaluation").Range("I13:L13").Value
Worksheets("MRep3").Range("B23:E23").Value =
Worksheets("Evaluation").Range("N12:Q12").Value
Worksheets("MRep3").Range("B24:E24").Value =
Worksheets("Evaluation").Range("N13:Q13").Value
Worksheets("MRep3").Range("B31:E31").Value =
Worksheets("Evaluation").Range("S12:V12").Value
Worksheets("MRep3").Range("B32:E32").Value =
Worksheets("Evaluation").Range("S13:V13").Value
Worksheets("MRep3").Range("B41:E41").Value =
Worksheets("Evaluation").Range("I28:L28").Value
Worksheets("MRep3").Range("B49:E49").Value =
Worksheets("Evaluation").Range("N28:Q28").Value
Worksheets("MRep3").Range("B50:E50").Value =
Worksheets("Evaluation").Range("N29:Q29").Value
Worksheets("MRep3").Range("A15").Value =
Worksheets("Evaluation").Range("G25").Value
Worksheets("MRep3").Range("A23").Value =
Worksheets("Evaluation").Range("G25").Value
Worksheets("MRep3").Range("A31").Value =
Worksheets("Evaluation").Range("G25").Value
Worksheets("MRep3").Range("A41").Value =
Worksheets("Evaluation").Range("I29").Value
Worksheets("MRep4").Range("A9").Value =
Worksheets("Evaluation").Range("W5").Value
Worksheets("MRep4").Range("A16").Value =
Worksheets("Evaluation").Range("W11").Value
Worksheets("MRep4").Range("A23").Value =
Worksheets("Evaluation").Range("W17").Value
Worksheets("MRep4").Range("A30").Value =
Worksheets("Evaluation").Range("W23").Value
Worksheets("MRep4").Range("A37").Value =
Worksheets("Evaluation").Range("W29").Value
End Select
End Sub


.

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default I need smaller code.
Not a problem. :)
if it doesn't work, let me know.

"Jennifer Glass" wrote in message
...
Thank You!

-----Original Message-----
You can use this as a guide:

Option Explicit
Private Sub Up1_Click()
Select Case LB1.ListIndex
Case 0 'First item
Call WriteValues(LB1.ListIndex)
End Select
End Sub

Public Function WriteValues(TheCase As Integer)
Dim ls_RangeString As String
Dim ll_RowNumber As Long

ll_RowNumber = 28 + (TheCase * 40)
ls_RangeString = "P" + CStr(ll_RowNumber) + ":U" +
CStr(ll_RowNumber)
Worksheets("MRep1").Range(ls_RangeString).Value =
Worksheets("Evaluation").Range("B17:G17").Value
End Function

Just add the extra case statements to the Up1_Click to
accomidate, then do
the following in WriteValues for each line:
change the "28" to the row number
adjust the "ls_RangeString = ..." line so
that it conforms with
the current line you're writing from
add ls_RangeString as your range in the line

This will at least cut down the amount of coding you'll
have to enter a
little bit.

Tom S.

"Jennifer Glass"
wrote in message
...
Hi,

I am trying to put some code on a command button that
will
move data depending on what value is chosen from in a
list
box. Problem is it's alot of data and I've only written
one case and it's really long. I need 12 cases in all
and
that would make it too long and hard to create. I'm
pretty
new to visual basic and I've heard of looping and I
don't
know if I fully understand it yet. Could anyone help me?
Basically what I need is to create the following case 11
more times. The destination cells columns rows and
ranges
will be the same but the source rows should increase by
40
rows. Everything else stays the same. Here the first
case
in my code....Can anyone help me with what my code
should
look like?

Option Explicit
Private Sub Up1_Click()
Select Case LB1.ListIndex
Case 0 'First item
Worksheets("MRep1").Range("P28:U28").Value =
Worksheets("Evaluation").Range("B17:G17").Value
Worksheets("MRep1").Range("P27:U27").Value =
Worksheets("Evaluation").Range("B25:G25").Value
Worksheets("MRep1").Range("P29:U29").Value =
Worksheets("Evaluation").Range("B26:G26").Value
Worksheets("MRep1").Range("D28:I28").Value =
Worksheets("Evaluation").Range("B18:G18").Value
Worksheets("MRep1").Range("D27:I27").Value =
Worksheets("Evaluation").Range("B23:G23").Value
Worksheets("MRep1").Range("D29:I29").Value =
Worksheets("Evaluation").Range("B24:G24").Value
Worksheets("MRep1").Range("J54:O54").Value =
Worksheets("Evaluation").Range("B7:G7").Value
Worksheets("MRep1").Range("J55:O55").Value =
Worksheets("Evaluation").Range("B12:G12").Value
Worksheets("MRep1").Range("J56:O56").Value =
Worksheets("Evaluation").Range("B27:G27").Value
Worksheets("MRep1").Range("D55").Value =
Worksheets("Evaluation").Range("G13").Value
Worksheets("MRep1").Range("E55").Value =
Worksheets("Evaluation").Range("G8").Value
Worksheets("MRep1").Range("S54").Value =
Worksheets("Evaluation").Range("I5").Value
Worksheets("MRep1").Range("T54").Value =
Worksheets("Evaluation").Range("N5").Value
Worksheets("MRep1").Range("U54").Value =
Worksheets("Evaluation").Range("S5").Value
Worksheets("MRep1").Range("V54").Value =
Worksheets("Evaluation").Range("I10").Value
Worksheets("MRep1").Range("W54").Value =
Worksheets("Evaluation").Range("N10").Value
Worksheets("MRep1").Range("X54").Value =
Worksheets("Evaluation").Range("S10").Value
Worksheets("MRep1").Range("S55").Value =
Worksheets("Evaluation").Range("I7").Value
Worksheets("MRep1").Range("T55").Value =
Worksheets("Evaluation").Range("N7").Value
Worksheets("MRep1").Range("U55").Value =
Worksheets("Evaluation").Range("S7").Value
Worksheets("MRep1").Range("V55").Value =
Worksheets("Evaluation").Range("I12").Value
Worksheets("MRep1").Range("W55").Value =
Worksheets("Evaluation").Range("N12").Value
Worksheets("MRep1").Range("X55").Value =
Worksheets("Evaluation").Range("S12").Value
Worksheets("MRep1").Range("S56").Value =
Worksheets("Evaluation").Range("I8").Value
Worksheets("MRep1").Range("T56").Value =
Worksheets("Evaluation").Range("N8").Value
Worksheets("MRep1").Range("U56").Value =
Worksheets("Evaluation").Range("S8").Value
Worksheets("MRep1").Range("V56").Value =
Worksheets("Evaluation").Range("I13").Value
Worksheets("MRep1").Range("W56").Value =
Worksheets("Evaluation").Range("N13").Value
Worksheets("MRep1").Range("X56").Value =
Worksheets("Evaluation").Range("S13").Value
Worksheets("MRep2").Range("D52:I52").Value =
Worksheets("Evaluation").Range("B18:G18").Value
Worksheets("MRep2").Range("D53:I53").Value =
Worksheets("Evaluation").Range("B28:G28").Value
Worksheets("MRep2").Range("D54:I54").Value =
Worksheets("Evaluation").Range("B29:G29").Value
Worksheets("MRep2").Range("P52:U52").Value =
Worksheets("Evaluation").Range("B30:G30").Value
Worksheets("MRep2").Range("P53:U53").Value =
Worksheets("Evaluation").Range("B31:G31").Value
Worksheets("MRep2").Range("F28").Value =
Worksheets("Evaluation").Range("B33").Value
Worksheets("MRep2").Range("G28").Value =
Worksheets("Evaluation").Range("B34").Value
Worksheets("MRep2").Range("F29").Value =
Worksheets("Evaluation").Range("B35").Value
Worksheets("MRep2").Range("G29").Value =
Worksheets("Evaluation").Range("B36").Value
Worksheets("MRep2").Range("R26").Value =
Worksheets("Evaluation").Range("F33").Value
Worksheets("MRep2").Range("R27").Value =
Worksheets("Evaluation").Range("F34").Value
Worksheets("MRep2").Range("R28").Value =
Worksheets("Evaluation").Range("F35").Value
Worksheets("MRep2").Range("R29").Value =
Worksheets("Evaluation").Range("F36").Value
Worksheets("MRep3").Range("B11").Value =
Worksheets("Evaluation").Range("I10").Value
Worksheets("MRep3").Range("B19").Value =
Worksheets("Evaluation").Range("N10").Value
Worksheets("MRep3").Range("B27").Value =
Worksheets("Evaluation").Range("S10").Value
Worksheets("MRep3").Range("F11").Value =
Worksheets("Evaluation").Range("J14").Value
Worksheets("MRep3").Range("F19").Value =
Worksheets("Evaluation").Range("O14").Value
Worksheets("MRep3").Range("F27").Value =
Worksheets("Evaluation").Range("T14").Value
Worksheets("MRep3").Range("H11").Value =
Worksheets("Evaluation").Range("H16").Value
Worksheets("MRep3").Range("H19").Value =
Worksheets("Evaluation").Range("M16").Value
Worksheets("MRep3").Range("H27").Value =
Worksheets("Evaluation").Range("R16").Value
Worksheets("MRep3").Range("M11").Value =
Worksheets("Evaluation").Range("H21").Value
Worksheets("MRep3").Range("M19").Value =
Worksheets("Evaluation").Range("M21").Value
Worksheets("MRep3").Range("M27").Value =
Worksheets("Evaluation").Range("R21").Value
Worksheets("MRep3").Range("B37").Value =
Worksheets("Evaluation").Range("I26").Value
Worksheets("MRep3").Range("F37").Value =
Worksheets("Evaluation").Range("J30").Value
Worksheets("MRep3").Range("H37").Value =
Worksheets("Evaluation").Range("H32").Value
Worksheets("MRep3").Range("M37").Value =
Worksheets("Evaluation").Range("H37").Value
Worksheets("MRep3").Range("F46").Value =
Worksheets("Evaluation").Range("O30").Value
Worksheets("MRep3").Range("H46").Value =
Worksheets("Evaluation").Range("M32").Value
Worksheets("MRep3").Range("M46").Value =
Worksheets("Evaluation").Range("M37").Value
Worksheets("MRep3").Range("B15:E15").Value =
Worksheets("Evaluation").Range("I12:L12").Value
Worksheets("MRep3").Range("B16:E16").Value =
Worksheets("Evaluation").Range("I13:L13").Value
Worksheets("MRep3").Range("B23:E23").Value =
Worksheets("Evaluation").Range("N12:Q12").Value
Worksheets("MRep3").Range("B24:E24").Value =
Worksheets("Evaluation").Range("N13:Q13").Value
Worksheets("MRep3").Range("B31:E31").Value =
Worksheets("Evaluation").Range("S12:V12").Value
Worksheets("MRep3").Range("B32:E32").Value =
Worksheets("Evaluation").Range("S13:V13").Value
Worksheets("MRep3").Range("B41:E41").Value =
Worksheets("Evaluation").Range("I28:L28").Value
Worksheets("MRep3").Range("B49:E49").Value =
Worksheets("Evaluation").Range("N28:Q28").Value
Worksheets("MRep3").Range("B50:E50").Value =
Worksheets("Evaluation").Range("N29:Q29").Value
Worksheets("MRep3").Range("A15").Value =
Worksheets("Evaluation").Range("G25").Value
Worksheets("MRep3").Range("A23").Value =
Worksheets("Evaluation").Range("G25").Value
Worksheets("MRep3").Range("A31").Value =
Worksheets("Evaluation").Range("G25").Value
Worksheets("MRep3").Range("A41").Value =
Worksheets("Evaluation").Range("I29").Value
Worksheets("MRep4").Range("A9").Value =
Worksheets("Evaluation").Range("W5").Value
Worksheets("MRep4").Range("A16").Value =
Worksheets("Evaluation").Range("W11").Value
Worksheets("MRep4").Range("A23").Value =
Worksheets("Evaluation").Range("W17").Value
Worksheets("MRep4").Range("A30").Value =
Worksheets("Evaluation").Range("W23").Value
Worksheets("MRep4").Range("A37").Value =
Worksheets("Evaluation").Range("W29").Value
End Select
End Sub


.



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Smaller worksheet david[_2_] New Users to Excel 3 August 7th 08 03:47 PM
can this be made smaller? fivermsg Excel Discussion (Misc queries) 4 March 15th 06 04:56 PM
Can this be made smaller? fivermsg Excel Discussion (Misc queries) 2 March 12th 06 06:55 PM
smaller equations: a & b, or just a.. nastech Excel Discussion (Misc queries) 6 January 29th 06 11:34 PM
Time smaller than a second PK[_6_] Excel Programming 3 October 11th 03 05:06 AM


All times are GMT +1. The time now is 02:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement