Dim a range: confusion

irresistible007

Board Regular
Joined
Nov 24, 2005
Messages
173
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
try;
Code:
Private Sub cmd_ok_Click()
Range("g" & Rows.Count).End(xlUp).Offset(1) = Txt1.Text
Txt1.Text = ""
End Sub
 
Upvote 0
Hi,

Try this code:

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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Code:
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...
 
Upvote 0

Forum statistics

Threads
1,217,383
Messages
6,136,267
Members
450,001
Latest member
KWeekley08

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top