edit values of a cell through textbox in a user form

[ronlaude]

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:

[VBA]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[/VBA]

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

[VBA]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[/VBA]

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

[Teeroy]

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:

[vba]
'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


[/vba]

[CatDaddy]

missing second end if

[Paul_Hossler]

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



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

[/vba]



[vba]
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
[/vba]

Paul

[ronlaude]

thanks so much for the helP....