(Noob) Range Question + UserForm

[Alphacsulb]

TextBox1 in my userform is a row I input a number into.

I'm having a problem identifying it as a range though. Here is my code:

Private Sub CommandButton1_Click()
  MyRowNumber = TextBox1.Value
  
[COLOR=Red]'Here is where the problem starts, it says compile error, syntax error[/COLOR]
Range("DU" & MyRowNumber:"IC" & MyRowNumber).Select
    Range("IC" & MyRowNumber).Activate
    Selection.ClearContents
  
  
 'This reference here works
 TextBox2.Text = Range("IB" & MyRowNumber).Value
End Sub

Second question is, what is it called when you reference the "MrRowNumber" like I did here?

 

[Smitty]

You're missing some bits:

Range("DU" & MyRowNumber & ":IC" & MyRowNumber).Select

And note that you don't need to select anything:

Range("DU" & MyRowNumber & ":IC" & MyRowNumber).ClearContents

HTH,

 

[njimack]

You're seeing that error because you need to include the colon within the quotes:

Range("DU" & MyRowNumber &":IC" & MyRowNumber).ClearContents

Although you could also use:

Range(Cells(MyRowNumber,125),Cells(MyRowNumber,237)).ClearContents

 

[Alphacsulb]

Smitty, that worked thanks.

Here is a follow up, can I reference the MyRowNumber in different modules if it's already in this Private Sub, or do I need to state it in every macro/module?

 

[Smitty]

Here is a follow up, can I reference the MyRowNumber in different modules if it's already in this Private Sub, or do I need to state it in every macro/module?

Sure, just declare it as public outside of any procedures in a general module:

Public MyRowNumber as Long

Then you can use it anywhere in the workbook with out re-declaring it. You can also pass the value between subs. Check out "Lifetime and Scope of Variables" in the VB helpfile.

 

[Alphacsulb]

Does that go before or after:

MyRowNumber = TextBox1.Value

 

[Smitty]

It goes before any procedures, but after Option Explicit:

<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Public</SPAN> MyRowNumber <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> foo()<br><SPAN style="color:#007F00">'    yada</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>