PDA

View Full Version : Solved: excel range



chungtinhlak
12-06-2008, 07:12 PM
Hello all, I have 8 text box where user input the column of there data. this is my code, how come it doesn't take it

Range("UserForm1.txtLineNumber & ":" & UserForm1.txtLineNumber, _
UserForm1.txtWorkStation & ":" & UserForm1.txtWorkStation, _
UserForm1.txtMTComments & ":" & UserForm1.txtMTComments, _
UserForm1.txtQTInitiated & ":" & UserForm1.txtQTInitiated, _
UserForm1.txtQTStatus & ":" & UserForm1.txtQTStatus, _
UserForm1.txtQTCompleted & ":" & UserForm1.txtQTCompleted, _
UserForm1.txtwhencreated & ":" & UserForm1.txtwhencreated, _
UserForm1.txtWhenUpdated & ":" & UserForm1.txtWhenUpdated").Select


thanks

lucas
12-06-2008, 07:33 PM
try this for selecting your range:
Option Explicit
Sub selectRange()
Dim rng As Range
Set rng = Application.InputBox("Select the target range with the mouse", Type:=8)
If rng Is Nothing Then Exit Sub
rng.Select
End Sub

chungtinhlak
12-06-2008, 07:40 PM
I can't select the range with the mouse because it's on the textbox. Don't quite get it. Sorry, I'm a real noob.

GTO
12-06-2008, 07:42 PM
Greeings chungtinhlak,

First off I would like to welcome you, as it appears you just joined. I can assure you that you can received the best of help here, as there are some great folks that will go out of their way to answer most any problem.

Now as to your question, I am afraid what you are trying to do is most unclear. About the only thing I can discern is that you're trying to select a range?

Anyways, please attach an example workbook with your userform in it, and give a description as to what is supposed to happen, and howo or what is supposed to cause the "happening." For instance, "After the user enters values in text boxes: txtLineNumber, txtWorkStation, etc, - then the user would click the <OK> button. This would cause..." (the values to be entered into certain cells or whatever the goal is)

I think that would give us a better chance at being helpful,

Mark

Kenneth Hobs
12-06-2008, 08:08 PM
The parameter Type with a value of 8 in Application.Inputbox allows you to select the range.

Click outside the dialog and drag the range to select it and the inputbox will update to the range string selected. Click OK to exit the dialog.

chungtinhlak
12-06-2008, 08:19 PM
GTO,

Thanks for the greeting, :)

The file is too big to attach here.

Let me see if I can explain it more clearly.

People are sending me all these files, the content is the same but the columns are also off. Therefore, I created a userform with textbox, (txtworkstation,txtqtinitianted...and so on", the purpose of this is that, when I enter in these column, the vba code (below) will copy and paste these column toware the end, this way, all these column will always be in the order that I need it too to perform all my calculation. After successfully move all the columns toward the end, I want to delete the old ones because they're just duplicate.

Here's the complete code.

Range(UserForm1.txtLineNumber & ":" & UserForm1.txtLineNumber).Select
Selection.Cut
Range("O:O").Select
ActiveSheet.Paste
'Work Station
Range(UserForm1.txtWorkStation & ":" & UserForm1.txtWorkStation).Select
Selection.Cut
Range("P:P").Select
ActiveSheet.Paste
'MT Comment
Range(UserForm1.txtMTComments & ":" & UserForm1.txtMTComments).Select
Selection.Cut
Range("Q:Q").Select
ActiveSheet.Paste
'QT Initiated
Range(UserForm1.txtQTInitiated & ":" & UserForm1.txtQTInitiated).Select
Selection.Cut
Range("R:R").Select
ActiveSheet.Paste
'QTStatus
Range(UserForm1.txtQTStatus & ":" & UserForm1.txtQTStatus).Select
Selection.Cut
Range("S:S").Select
ActiveSheet.Paste
'TXTQTCompleted
Range(UserForm1.txtQTCompleted & ":" & UserForm1.txtQTCompleted).Select
Selection.Cut
Range("T:T").Select
ActiveSheet.Paste
'TXTWhenCreated
Range(UserForm1.txtwhencreated & ":" & UserForm1.txtwhencreated).Select
Selection.Cut
Range("U:U").Select
ActiveSheet.Paste
'TXTWhenUpdated
Range(UserForm1.txtWhenUpdated & ":" & UserForm1.txtWhenUpdated).Select
Selection.Cut
Range("V:V").Select
ActiveSheet.Paste



''''''''''''''It works to this point''''''''''''''''




Range("UserForm1.txtLineNumber & ":" & UserForm1.txtLineNumber, _
UserForm1.txtWorkStation & ":" & UserForm1.txtWorkStation, _
UserForm1.txtMTComments & ":" & UserForm1.txtMTComments, _
UserForm1.txtQTInitiated & ":" & UserForm1.txtQTInitiated, _
UserForm1.txtQTStatus & ":" & UserForm1.txtQTStatus, _
UserForm1.txtQTCompleted & ":" & UserForm1.txtQTCompleted, _
UserForm1.txtwhencreated & ":" & UserForm1.txtwhencreated, _
UserForm1.txtWhenUpdated & ":" & UserForm1.txtWhenUpdated").Select
Selection.Delete Shift:=ToLeft

Kenneth Hobs
12-06-2008, 08:33 PM
Can you show what you expect the string in the Range() to resolve to?

For one thing, the first and last quotes in the string for your long Range() should be deleted. Also, if an editbox value is A1 then A1:A1 is redundant. Just use A1 without the ":" if you are doing discontinuous ranges.

chungtinhlak
12-06-2008, 08:51 PM
as a default value

UserForm1.txtwhencreated.Value = "L"
UserForm1.txtQTInitiated.Value = "G"
UserForm1.txtQTCompleted.Value = "J"
UserForm1.txtWhenUpdated.Value = "N"
UserForm1.txtLineNumber.Value = "B"
UserForm1.txtWorkStation.Value = "D"
UserForm1.txtMTComments.Value = "E"
UserForm1.txtQTStatus.Value = "H"

So my goal on this
Range("UserForm1.txtLineNumber & ":" & UserForm1.txtLineNumber, _
UserForm1.txtWorkStation & ":" & UserForm1.txtWorkStation, _
UserForm1.txtMTComments & ":" & UserForm1.txtMTComments, _
UserForm1.txtQTInitiated & ":" & UserForm1.txtQTInitiated, _
UserForm1.txtQTStatus & ":" & UserForm1.txtQTStatus, _
UserForm1.txtQTCompleted & ":" & UserForm1.txtQTCompleted, _
UserForm1.txtwhencreated & ":" & UserForm1.txtwhencreated, _
UserForm1.txtWhenUpdated & ":" & UserForm1.txtWhenUpdated").Select
Selection.Delete Shift:=ToLeft

is to make it become

Range("L:L,G:G,J:J....and so on) but the user can change that.

Kenneth Hobs
12-06-2008, 09:39 PM
You have an obvious syntax error. It is unmactched quotes.
VBE will mark the text in red if you have unmatched quotes. Since I am lazy, I won't create a userform with editboxes named like yours to test. Instead, this shows something similar to what you had in short format.
Sub test()
Dim a, b, c
a = "L"
b = "G"
c = "J"
Range("a & ":" & a, & b & ":" & b, & c & ":" c")
End Sub

Notice how I removed the quotes from the ends and added quotes around the commas. If you don't know how a string is resolving, step through the code with F8 or use MsgBox or debug.print. Debug.print shows the result in the VBE Immediate window.
Sub test()
Dim a, b, c, s
a = "L"
b = "G"
c = "J"
s = a & ":" & a & "," & b & ":" & b & "," & c & ":" & c
Debug.Print s
MsgBox Range(s).Address
Range(s).Select
End Sub
Not sure why you are doing this but selecting or setting whole columns is generally not the most efficient method.

chungtinhlak
12-06-2008, 09:58 PM
Kenneth,

Thanks so much for you help

GTO
12-06-2008, 10:34 PM
Sorry, on top of being a slow typist, laptop decided to crash...

Anyways, along the same line as what Kenneth points out as to efficiency, this may not be the ultimate solution, but... since I had her about done (pre-crash), here you go, in case of use.

In looking at what I think the goal is, it seemed to me that an area of concern would be if the user skips a textbox, or elsewise, enters something goofy. This would seem to leave the resultant string unusable.

Anyways, in the attached workbook, you can see that I used combo boxes, and made a bit of a "safety" in building the string that you want to delineate the range with.
Private Sub cmdOKBttn_Click()
Dim ctl As Control
Dim strRange As String

For Each ctl In Me.Controls

'// for each combo box named LIKE "cboCol_1" (or 2,3,etc) //
If ctl.Name Like "cboCol_#" Then

'// if there's a valid value in it, add (concentate) it to the string //
'// being built. //
If Not ctl.Value = vbNullString Then

strRange = strRange & ctl.Value & ","

End If
End If
Next

'// Then strip off the last comma. //
strRange = Left(strRange, Len(strRange) - 1)

'// Copy the non-contiguous range to where you want it //
Range(strRange).Copy Range("O1")

'//...and delete the "original" columns. //
Range(strRange).Delete xlLeft

End Sub

Hope this helps,

Mark

chungtinhlak
12-06-2008, 11:01 PM
Thanks Gto, I'm still trying to read your code and digest, :) I'm really noob at this, I have 4 differnt books on my desk right now trying to learn these things. Thanks a lot for all your help. You guys are great, wish I found you guys sooner.

:)

GTO
12-06-2008, 11:46 PM
You are most welome, and if after looking at it, you have further questions, I would suggest posting back in this thread, as the continuity helps any "answerer" on target with progress thus far.

A final thought (for tonight at least) would be something that I have always found helpful when deciphering someone else's code. If you (while in the userform's code window) use the F8 button repeatedly, you can step-thru ea step the the code does. Sometimes this helps me to see how a loop is working, or how a string is being built, etc.

Anyways, just thought to mention that, as it was (and still is sometimes) the way I figure out what someone's code is doing.

Have a great night,

Mark

Kenneth Hobs
12-07-2008, 12:29 AM
GTO's code is a fine example for several techniques. You will probably want to add Unload Me to the end of the command buttons code.

Here is the Activate event for the userform in GTO's example. The Initialize event is what I typically use to prefill control values and setups.
Private Sub UserForm_Activate()
Dim ctl As Control

For Each ctl In Me.Controls
If ctl.Name Like "cboCol_#" Then
ctl.Clear
ctl.List = _
Array("A:A", "B:B", "C:C", "D:D", "E:E", "F:F", "G:G", "H:H", "I:I", "J:J", "K:K", "L:L")
End If
Next

End Sub
Here is something that I worked up as another short example. It is not as neat as GTO's but shows another way to do it. With this method, if any of the variables result in an invalid range, the error label kicks in. I would recommend though that you add code to the LostFocus event for each control if you need to validate proper entries.
Sub Test()
Dim r As Range
Dim a As String, b As String, c As String, d As String
On Error GoTo SaySo
a = "E"
b = "G"
c = "F"
d = "Not a Valid Column Letter"
SetUnion:
Set r = Union(Columns(CN(a)), Columns(CN(b)), Columns(CN(c)), Columns(CN(d)))
If r Is Nothing Then
MsgBox "The union r is nothing."
Else: MsgBox r.Address
End If

Exit Sub
SaySo:
MsgBox "Invalid selection for range."
d = "B"
GoTo SetUnion
End Sub


Function CN(sColumnLetter As String) As Long
CN = Cells(1, sColumnLetter).Column
End Function

chungtinhlak
12-07-2008, 11:07 AM
thank you

lucas
12-07-2008, 12:38 PM
chungtinhlak, if you found a solution please mark your thread solved using the thread tools at the top of the page.