how to loop through a code with different cell references

R

renicemae

New Member
Joined
Jan 18, 2016
Messages
5
Hi!

I'm new to this forum and would like to ask for your help regarding my project. I have a project which is running fine but my codes are too long that for me is not practical.

I have 43 x 6 textboxes where the user will input values from. The said values will be send to a range of cells say: C18:I66 when the combobox value is January.

The problem here is I want to repeat the process if the user select February in the combobox but the values will go to another range of cells.

My code:

Private Sub cmduer_Click()

If cbr.Value = "January 2016" Then
Range("S18").Activate

Range("S18").Value = Range("S18").Value + t01.Text
Range("T18").Value = Range("T18").Value + t02.Text
Range("U18").Value = Range("U18").Value + t03.Text
Range("V18").Value = Range("V18").Value + t04.Text
Range("W18").Value = Range("W18").Value + t05.Text
Range("X18").Value = Range("X18").Value + t06.Text

Range("S19").Value = Range("S19").Value + t11.Text
Range("T19").Value = Range("T19").Value + t12.Text
Range("U19").Value = Range("U19").Value + t13.Text
Range("V19").Value = Range("V19").Value + t14.Text
Range("W19").Value = Range("W19").Value + t15.Text
Range("X19").Value = Range("X19").Value + t16.Text

Range("S20").Value = Range("S20").Value + tex1.Text
Range("T20").Value = Range("T20").Value + tex2.Text
Range("U20").Value = Range("U20").Value + tex3.Text
Range("V20").Value = Range("V20").Value + tex4.Text
Range("W20").Value = Range("W20").Value + tex5.Text
Range("X20").Value = Range("X20").Value + tex6Text

Range("S21").Value = Range("S21").Value + tmx1.Text
Range("T21").Value = Range("T21").Value + tmx2.Text
Range("U21").Value = Range("U21").Value + tmx3.Text
Range("V21").Value = Range("V21").Value + tmx4.Text
Range("W21").Value = Range("W21").Value + tmx5.Text
Range("X21").Value = Range("X21").Value + tmx6.Text

Range("S22").Value = Range("S22").Value + trt1.Text
Range("T22").Value = Range("T22").Value + trt2.Text
Range("U22").Value = Range("U22").Value + trt3.Text
Range("V22").Value = Range("V22").Value + trt4.Text
Range("W22").Value = Range("W22").Value + trt5.Text
Range("X22").Value = Range("X22").Value + trt6.Text

Range("S23").Value = Range("S23").Value + tq1.Text
Range("T23").Value = Range("T23").Value + tq2.Text
Range("U23").Value = Range("U23").Value + tq3.Text
Range("V23").Value = Range("V23").Value + tq4.Text
Range("W23").Value = Range("W23").Value + tq5.Text
Range("X23").Value = Range("X23").Value + tq6.Text

Range("S24").Value = Range("S24").Value + tm1.Text
Range("T24").Value = Range("T24").Value + tm2.Text
Range("U24").Value = Range("U24").Value + tm3.Text
Range("V24").Value = Range("V24").Value + tm4.Text
Range("W24").Value = Range("W24").Value + tm5.Text
Range("X24").Value = Range("X24").Value + tm6.Text

End If

If cbr.Value = "February 2016" Then
Range("AC18").Activate

Range("AC18").Value = Range("AC18").Value + t01.Text
Range("AD18").Value = Range("AD18").Value + t02.Text
Range("AE18").Value = Range("AE18").Value + t03.Text
Range("AF18").Value = Range("AF18").Value + t04.Text
Range("AG18").Value = Range("AG18").Value + t05.Text
Range("AH18").Value = Range("AH18").Value + t06.Text

Range("AC19").Value = Range("AC19").Value + t11.Text
Range("AD19").Value = Range("AD19").Value + t12.Text
Range("AE19").Value = Range("AE19").Value + t13.Text
Range("AF19").Value = Range("AF19").Value + t14.Text
Range("AG19").Value = Range("AG19").Value + t15.Text
Range("AH19").Value = Range("AH19").Value + t16.Text

Range("AC20").Value = Range("AC20").Value + tex1.Text
Range("AD20").Value = Range("AD20").Value + tex2.Text
Range("AE20").Value = Range("AE20").Value + tex3.Text
Range("AF20").Value = Range("AF20").Value + tex4.Text
Range("AG20").Value = Range("AG20").Value + tex5.Text
Range("AH20").Value = Range("AH20").Value + tex6Text

Range("AC21").Value = Range("AC21").Value + tmx1.Text
Range("AD21").Value = Range("AD21").Value + tmx2.Text
Range("AE21").Value = Range("AE21").Value + tmx3.Text
Range("AF21").Value = Range("AF21").Value + tmx4.Text
Range("AG21").Value = Range("AG21").Value + tmx5.Text
Range("AH21").Value = Range("AH21").Value + tmx6.Text

Range("AC22").Value = Range("AC22").Value + trt1.Text
Range("AD22").Value = Range("AD22").Value + trt2.Text
Range("AE22").Value = Range("AE22").Value + trt3.Text
Range("AF22").Value = Range("AF22").Value + trt4.Text
Range("AG22").Value = Range("AG22").Value + trt5.Text
Range("AH22").Value = Range("AH22").Value + trt6.Text

Range("AC23").Value = Range("AC23").Value + tq1.Text
Range("AD23").Value = Range("AD23").Value + tq2.Text
Range("AE23").Value = Range("AE23").Value + tq3.Text
Range("AF23").Value = Range("AF23").Value + tq4.Text
Range("AG23").Value = Range("AG23").Value + tq5.Text
Range("AH23").Value = Range("AH23").Value + tq6.Text

Range("AC24").Value = Range("AC24").Value + tm1.Text
Range("AD24").Value = Range("AD24").Value + tm2.Text
Range("AE24").Value = Range("AE24").Value + tm3.Text
Range("AF24").Value = Range("AF24").Value + tm4.Text
Range("AG24").Value = Range("AG24").Value + tm5.Text
Range("AH24").Value = Range("AH24").Value + tm6.Text
End If

End sub

Imagine how long my code is...I repeat that process until december thats why i want to make it short. i want to repeat this process in March to December but with different range of cells. Can anyone help me out? Thank you so much for the time.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
You have:
I have 43 x 6 textboxes
Are you saying you have 258 text boxes?
Are these text boxes on a Userform?

Please tell us what your attempting to do here.
Your surely not going to write this much scripting for all twelve months.
What will be put into these text boxes?
 
Upvote 0
Would you check if this works on your machine?



Code: 
Private Sub cmduer_Click()
Dim i As Integer
Dim j As Integer
Dim RngName(18 To 24) As String

RngName(18) = "t0"
RngName(19) = "t1"
RngName(20) = "tex"
RngName(21) = "tmx"
RngName(22) = "trt"
RngName(23) = "tq"
RngName(24) = "tm"


  If cbr.Value = "January 2016" Then
   Range("S18").Activate
   For i = 1 To 6
    For j = 18 To 24
     Cells(j, 17 + i) = Cells(j, 17 + i) & Range(RngName(j) & i)
    Next j
   Next i

  End If

  If cbr.Value = "February 2016" Then
   Range("AC18").Activate
  
   Range("AC18").Value = Range("AC18").Value + t01.Text
   Range("AD18").Value = Range("AD18").Value + t02.Text
   Range("AE18").Value = Range("AE18").Value + t03.Text
   Range("AF18").Value = Range("AF18").Value + t04.Text
   Range("AG18").Value = Range("AG18").Value + t05.Text
   Range("AH18").Value = Range("AH18").Value + t06.Text
  
   Range("AC19").Value = Range("AC19").Value + t11.Text
   Range("AD19").Value = Range("AD19").Value + t12.Text
   Range("AE19").Value = Range("AE19").Value + t13.Text
   Range("AF19").Value = Range("AF19").Value + t14.Text
   Range("AG19").Value = Range("AG19").Value + t15.Text
   Range("AH19").Value = Range("AH19").Value + t16.Text
  
   Range("AC20").Value = Range("AC20").Value + tex1.Text
   Range("AD20").Value = Range("AD20").Value + tex2.Text
   Range("AE20").Value = Range("AE20").Value + tex3.Text
   Range("AF20").Value = Range("AF20").Value + tex4.Text
   Range("AG20").Value = Range("AG20").Value + tex5.Text
   Range("AH20").Value = Range("AH20").Value + tex6Text
  
   Range("AC21").Value = Range("AC21").Value + tmx1.Text
   Range("AD21").Value = Range("AD21").Value + tmx2.Text
   Range("AE21").Value = Range("AE21").Value + tmx3.Text
   Range("AF21").Value = Range("AF21").Value + tmx4.Text
   Range("AG21").Value = Range("AG21").Value + tmx5.Text
   Range("AH21").Value = Range("AH21").Value + tmx6.Text
  
   Range("AC22").Value = Range("AC22").Value + trt1.Text
   Range("AD22").Value = Range("AD22").Value + trt2.Text
   Range("AE22").Value = Range("AE22").Value + trt3.Text
   Range("AF22").Value = Range("AF22").Value + trt4.Text
   Range("AG22").Value = Range("AG22").Value + trt5.Text
   Range("AH22").Value = Range("AH22").Value + trt6.Text
  
   Range("AC23").Value = Range("AC23").Value + tq1.Text
   Range("AD23").Value = Range("AD23").Value + tq2.Text
   Range("AE23").Value = Range("AE23").Value + tq3.Text
   Range("AF23").Value = Range("AF23").Value + tq4.Text
   Range("AG23").Value = Range("AG23").Value + tq5.Text
   Range("AH23").Value = Range("AH23").Value + tq6.Text
  
   Range("AC24").Value = Range("AC24").Value + tm1.Text
   Range("AD24").Value = Range("AD24").Value + tm2.Text
   Range("AE24").Value = Range("AE24").Value + tm3.Text
   Range("AF24").Value = Range("AF24").Value + tm4.Text
   Range("AG24").Value = Range("AG24").Value + tm5.Text
   Range("AH24").Value = Range("AH24").Value + tm6.Text
  End If

 End Sub

If so repeat for the second group but I think there may be easier ways for you to do this. First can this workbook be more dynamic? Range names with easy patterns etc? I know its a pain but it is very worth it. If you put more info I think what you are talking about is about 20 ish lines of code +-.
 
Upvote 0
Hi.. Sorry for the very late reply. Yes, I have that much txtboxes-258 and these are all placed in a userform.

Numbers will be put into txtboxes.
My proj. Goes like this.
I have 43 different form type and 6 different kinds of form type,hence 43x6.

The user will input integers into rhe txtboxes and those values should appear to cells say,D18:I18 and D34:I66 when Priority button and Non prioritybutton is pressed respectively.

Any existing integer values in the cells should add up to the current values of the txtboxes when those buttons are pressed.

Not all txtboxes are filled everytime ,so when the buttons are pressed,it should also ignore blank values.

Integer values of txtboxes should appear to 12 different sheets say, January to December but of the same range of cells I mentioned above when the user choose a month in a combobox.

I hope I give you enough info. About my proj. My Answer Is This. I'll really appreciate your time andeffort for helping me. Thank you in advance :)
 
Upvote 0
Hello HotRhodium,I tried using your code but it didnt work. The values in txtbox did not appear to the cells only the activate cell work out. Thank you for your help. Hope you could suggest me of more solutions? Thank you.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,530
Messages
6,125,353
Members
449,220
Latest member
Edwin_SVRZ

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