PDA

View Full Version : edit values of a cell through textbox in a user form



ronlaude
06-07-2012, 02:38 AM
hello everyone, i have a problem... i want to view and edit a value of a cell through a textbox in a user form.

i have used this code and it worked perfectly:

Private Sub CommandButton1_Click()
'Australia
If ComboBox1 = "Australia" Then
Set rngText1 = Range("A1")
End If
'China
If ComboBox1 = "China" Then
Set rngText1 = Worksheets("Sheet1").Range("A2")
End If
'India
If ComboBox1 = "India" Then
Set rngText1 = Worksheets("Sheet1").Range("A3")
End If
rngText1.Value = TextBox1.Text
End Sub

but my problem is when i tried to incorporate it in a large amount of information and i used a code like this:

With Workbooks(ThisWorkbook.Name).Worksheets("Sheet1")
For Each d In .Range("a3:a60000")
If d.Value = clientname1.Value Then
rmno1.Value = d.Offset(0, 26).Value
If rmno1.Value = d.Offset(0, 26).Value Then
ElseIf d.Value = "" Then
Exit For
End If
Next d
End With

nothing happens... can someone help me with this one? thanks!

Teeroy
06-07-2012, 06:01 AM
The code sample doesn't tell me what "clientname1" or "rmno1" are but I'm guessing from the context they are named ranges in the worksheet which can't be accessed in this manner. Try instead:


'testvalue and changermno are Range objects
Set testvalue = Range(ActiveWorkbook.Names("clientname1").Value)
Set changermno = Range(ActiveWorkbook.Names("rmno1").Value)

With Workbooks(ThisWorkbook.Name).Worksheets("Sheet1")

For Each d In .Range(Range(A3"), Range("A3").End(xlDown)) 'this selects used cells in a column rather than checking for a null string value
If d.Value = testvalue.Value Then
changermno.Value = d.Offset(0, 26).Value
'If rmno1.Value = d.Offset(0, 26).Value Then 'What are you trying to do????
End If
Next d
End With

CatDaddy
06-07-2012, 08:11 AM
missing second end if

Paul_Hossler
06-07-2012, 10:38 AM
1. First you make rmno1.Value = d.Offset(0, 26).Value

2. Then you test if rmno1.Value = d.Offset(0, 26).Value (always going to be True since you just made them equal on the line above)

3. But since there's no code in the If/Then block nothing is executed




rmno1.Value = d.Offset(0, 26).Value
If rmno1.Value = d.Offset(0, 26).Value Then
ElseIf d.Value = "" Then
Exit For
End If






rmno1.Value = d.Offset(0, 26).Value
If rmno1.Value = d.Offset(0, 26).Value Then
'do something

ElseIf d.Value = "" Then
Exit For
End If


Paul

ronlaude
06-17-2012, 03:27 PM
thanks so much for the helP....