How to get the respective data from 15 Cells of each row in a praticular column of sheet by pressing next button in User form in respective textboxes

[NimishK]

Hi

In Gen Module

Option Explicit
Public curRec As Integer
Public a$()
Public b$()
'------------------
in Userform1
Private Sub UserForm_Initialize()
curRec = 1
ReDim a$(1 To 15)
ReDim b$(1 To 15)


With Sheets("Sheet1")
     Userform1.textBox1.Text = .Cells(2, 3).Value   
     Userform1.textBox2.Text = .Cells(2, 4).Value 
     'The above reads the value in respective textbox  from Sheet1
     'FYI my range of data is from Column C to G and Row starts from 2 


  End with
End Sub

Private Sub cmdNext_Click()
   If curRec < 15 Then
         A$(curRec) = Userform1.textBox1.Text 
         B$(curRec) = Userform1.textBox2.Text 

              curRec = curRec + 1

          If curRec > UBound(A$) Then ReDim Preserve A$(1 To curRec)
           If curRec > UBound(B$) Then ReDim Preserve B$(1 To curRec)
          
          Userform1.textBox1.Text = A$(curRec) 
          Userform1.textBox2.Text = B$(curRec)
'What needs to be done here so that i am able to read the respective data from 15 Cells of each row  in a particular column of sheet by pressing next button in respective text boxes

 End If
End Sub

Thanks NimishK

 

[NdNoviceHlp]

What next 15 cells? Your textboxes are on 1 fixed address. U didn't say what U want to do with the data? Load it to an array and then what... put it somewhere on a sheet or show it on the userform? Anyways, I suspect that you want to scroll the "C" and "D" column data and then get the next 15 cells from the textbox value/address and display it somewhere? Maybe a bit more info.Dave

 

[NimishK]

Sorry my mistake in mentioning C to G instead of C toD. You got it right

My range is from Column C2 to Column D17 so 15 cells in each column
There are 2 textboxes.
textbox1.text to read the value of Column C2 to C17
textbox2.text to read the value of Column D2 to D17
so when pressing next-button want to read each values of cell from C2 to C17 in textbox1.text and simulatenously to read each value of cell from D2 to d17 in textbox2.text
and then load the same into the array


So basically
A$(curRec) = Userform1.textBox1.Text
B$(curRec) = Userform1.textBox2.Text
Do i change the above with
A$(curRec) = Worksheets("Sheet1"). Cells(curRec, 3).Value
but when form is initialised value of curRec = 1 but value in cell starts from row 2 Worksheets("Sheet1"). Cells(2, 3).Value
got stuck here
and how shall

Worksheets("Sheet1"). Cells(2, 3).Value = Userform1.textBox1.Text

 

[NdNoviceHlp]

textbox1.text to read the value of Column C2 to C17

All at once? "read each value" in textbox... what/how?
U always want the values in both textboxes to be on the same row for "C" and "D"?
Are U always loading the same array C2:D17?
I'm still not clear on what U want as the outcome? dave

 

[NimishK]

Pl correct the below as
textbox1.text to read the value of Column C2 to C16
textbox2.text to read the value of Column D2 to D16

 

[NimishK]

Not all at once but to read Each value from C2 to C16

so when pressing next-button want to read each values of cell from C2 to C16 in textbox1.text and simulatenously to read each value of cell from D2 to d16 in textbox2.text
and then load the same into the array

U always want the values in both textboxes to be on the same row for "C" and "D"?

Yes

My requirement is that when Pressing Next button it should read the next row record after C2 & D2 ie value of C3 in textbox1.text and D3 in textbox2.text respectively
Hope that makes it clear

 

[NdNoviceHlp]

OK and the array and output part? Dave

 

[NimishK]

OK and the array and output part? Dave

Are you trying to say that the above result which i desire can be achieved without array if yes then How ?

with array was trying to get data of EACH record from C2 to C16 to be displayed in Textbox1.text and EACH record from D2 to D16 to be displayed in Textbox2.Text

and in regards to output was that to paste the record which is seen in Textbox1.text and Textbox2.text into another sheet

 

[NimishK]

Also when pasting the record into another sheet could be modified in textbox1 and textbox2 and then pasted into another sheet. so that my original record stays intact in Sheet1

 

[NdNoviceHlp]

Ok. U can trial this. Requires a Userform1 with textbox1 and textbox2 and a commandbutton1. Data in sheet1 C2:D16 and Sheet2 for output. U didn't say where the output is going so it's going to sheet2 A1:B15. HTH. Dave
Userform1 code...

Option Explicit
Private Sub UserForm_Initialize()
UserForm1.TextBox1.Value = Sheets("sheet1").Cells(2, "C").Value
UserForm1.TextBox2.Value = Sheets("sheet1").Cells(2, "D").Value
End Sub

Private Sub CommandButton1_Click()
Dim CArr(15) As Variant, DArr(15) As Variant, Cnt As Integer, Temp As String
For Cnt = 0 To 14
CArr(Cnt) = Sheets("sheet1").Cells(Cnt + 2, "C")
DArr(Cnt) = Sheets("sheet1").Cells(Cnt + 2, "D")
Next Cnt
For Cnt = LBound(CArr) To UBound(CArr) - 1
UserForm1.TextBox1.Value = Sheets("sheet1").Cells(Cnt + 2, "C").Value
UserForm1.TextBox2.Value = Sheets("sheet1").Cells(Cnt + 2, "D").Value
If MsgBox(prompt:="Change: " & Sheets("sheet1").Cells(Cnt + 2, "C").Address _
  & "  Value: " & CArr(Cnt), Buttons:=vbYesNo, Title:="Change " & Sheets("sheet1").Cells(Cnt + 2, "C").Address & " ?") = vbYes Then
Temp = Application.InputBox("Enter new C Value.")
If Temp <> vbNullString And Temp <> "False" Then
CArr(Cnt) = Temp
End If
End If
If MsgBox(prompt:="Change: " & Sheets("sheet1").Cells(Cnt + 2, "D").Address _
  & "  Value: " & DArr(Cnt), Buttons:=vbYesNo, Title:="Change " & Sheets("sheet1").Cells(Cnt + 2, "D").Address & " ?") = vbYes Then
Temp = Application.InputBox("Enter new D Value.")
If Temp <> vbNullString And Temp <> "False" Then
DArr(Cnt) = Temp
End If
End If
Next Cnt
If MsgBox(prompt:="Do you want to review changes?", Buttons:=vbYesNo, Title:="Review Changes?") = vbYes Then
For Cnt = LBound(CArr) To UBound(CArr) - 1
UserForm1.TextBox1.Value = CArr(Cnt)
UserForm1.TextBox2.Value = DArr(Cnt)
MsgBox Sheets("sheet1").Cells(Cnt + 2, "C").Address & " C Value: " & CArr(Cnt) _
& "    " & Sheets("sheet1").Cells(Cnt + 2, "D").Address & " D Value: " & DArr(Cnt)
Next Cnt
End If
If MsgBox(prompt:="Do you want to transfer?", Buttons:=vbYesNo, Title:="Transfer?") = vbYes Then
For Cnt = LBound(CArr) To UBound(CArr) - 1
Sheets("sheet2").Cells(Cnt + 1, "A").Value = CArr(Cnt)
Sheets("sheet2").Cells(Cnt + 1, "B").Value = DArr(Cnt)
Next Cnt
End If
End Sub