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.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
try;
Code:
Private Sub cmd_ok_Click()
Range("g" & Rows.Count).End(xlUp).Offset(1) = Txt1.Text
Txt1.Text = ""
End Sub
 

abiram01

New Member
Joined
Aug 30, 2006
Messages
30
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
 

irresistible007

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

Active Member
Joined
Jun 27, 2006
Messages
345

ADVERTISEMENT

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

Board Regular
Joined
Nov 24, 2005
Messages
173

ADVERTISEMENT

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

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,123,089
Messages
5,599,667
Members
414,326
Latest member
Aerith

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
Top