Dim a range: confusion

[irresistible007]

Have placed a Text Box (lets say "Txt1") on top Left corner of my sheet and have placed a command button ("cmd_ok") beneath it...

Task which i would i like to perform is that whenever the command button is pressed after typing a certain text into the text box, it should pass the text to G1 (which is the first empty cell in the range i.e. Col "G") and reset the text box to blank for another entry... everytime it should pass the text to next empty cell in the range.

 

[agihcam]

try;

Private Sub cmd_ok_Click()
Range("g" & Rows.Count).End(xlUp).Offset(1) = Txt1.Text
Txt1.Text = ""
End Sub

 

[abiram01]

Hi,

Try this code:

Private Sub CommandButton1_Click()
Dim rng As Range

Set rng = ActiveWorkbook.Sheets("Sheet1").Range("G65536").End(xlUp)

rng.Offset(1, 0).Value = TextBox1.Text
TextBox1.Text=""
End Sub

 

[irresistible007]

though i have worked with code of abiram01 and it worked well... But there is a confusion in my mind which i want to clear so as i shouldn't have any troubles later...

what i am understanding from the code is that on clicking the button... the code will define a variable as a range and then defines its value as cell G1:G65536 and will then choose the uppermost cell from here i.e. G1 and then it will skip 1 row so as to finally sit on G2, kicks in the value from textbox.

But, when i saved and closed the file, and re-opened it and passed another value, it goes perfectly on G3, though i wanted the same... but i was thinking that it will go to G2 as clicking the button will again set the range G1:G65536 resulting it to overwrite the cell G2...

So this means that i am definetly miss-interpreting the code... can anyone explain me...plz

 

[patrickmuldoon99]

Set rng = ActiveWorkbook.Sheets("Sheet1").Range("G65536").End(xlUp)

This will set the variable rng (of type range) to the last cell that is not blank in your worksheet. It is then offset by one row to give the next available blank cell.

So it is similar to going to cell G65536 and pressing Ctrl + Up then going down a cell. The range type signifies that it is an address that is stored in the variable.

 

[irresistible007]

Ohhh man.... thanks buddy for saving me

 

[irresistible007]

one more thing... is "thisworkbook.sheets.." is appropiate one or "activeworkbook.sheets..."?

 

[Andrew Poulsom]

ThisWorkbook refers to the Workbook that contains the code that is running. ActiveWorkbook refers to the Workbook that is active.

Use ThisWorkbook if you always want your code to use the Workbook that contains it. Use ActiveWorkbook if you always want your code to use whichever Workbook is active.

 

[Greg Truby]

Actually, a couple of comments: (a) in theory agihcam's has a slight advantage over abiram's because it starts looking up column G based on the # of rows in the sheet and not 65536; which will change in the next version of Excel. I say "in theory" because from a pragmatic point of view it may be unlikely that your button will ever get clicked 65,000 times. But never know... (b) Yes, abiram's does make use of a range object variable. Which makes it all the more curious that he didn't go ahead and use that to test and handle the "I'm in the top row" situation. As is neither subroutine will put anything in G1 if that's the first empty cell in G:G. You can alter their code to be something like:

Private Sub cmd_ok_Click()
    Dim rngTarget As Range
    Set rngTarget = Range("G" & Rows.Count).End(xlUp)
    With rngTarget
        .Offset(IIf(IsEmpty(.Value), 0, 1)) = Me.Txt1.Text
    End With
    Me.Txt1.Text = vbNullString
End Sub

HTH<hr />Top o' the mornin' Mr. Poulsom...