View Full Version : Solved: range
fgarcia90
07-10-2012, 03:33 PM
Sorry if the question is too simple but i'm starting in vba
By the username in windows that i got in "user", see if it is from a group of persons in Colum A , row 1 to 20 and if so apply string "Tecnica", if it is in collum B, row 1 to 20 apply string "Comercial"
but the persons names don't appear in tecnicos and comerciais so it blocks in the if statement...
Thanks in advance...
Sub seccao()
Dim user As Variant
user =
Environ("USERNAME")
Dim seccao_type As String
Dim tecnicos, comerciais As Range
Set tecnicos = Sheet2.Range("A1:A20")
Set comerciais = Sheet2.Range("C1:C20")
If user = tecnicos Then
seccao_type = "Tecnica"
Else
user = comerciais Then
seccao_type = "Comercial"
End If
MsgBox seccao_type
End Sub
Greetings fgarcia :-)
I see that this is your first post, so let me be the first to welcome you to vbaexpress:hi:
If Google Translate picked up correctly, is that Portuguese?
As to your question: if the ranges stay decently small, you may wish to try MATCH() to find the value. Something like:
Option Explicit
Sub seccao()
Dim user As Variant
Dim seccao_type As String
Dim tecnicos, comerciais As Range
user = Environ("USERNAME")
Set tecnicos = Sheet2.Range("A1:A20")
Set comerciais = Sheet2.Range("C1:C20")
If Not IsError(Application.Match(user, tecnicos, 0)) Then
seccao_type = "Tecnica"
ElseIf Not IsError(Application.Match(user, comerciais, 0)) Then
seccao_type = "Comercial"
Else
seccao_type = "Not Found"
End If
MsgBox seccao_type
End Sub
Hope that helps,
Mark
Bob Phillips
07-11-2012, 01:09 AM
Query row 1
Sub seccao()
Dim user As String
Dim seccao_type As String
Dim rngUser As Range
Dim idxUser As Long
user = Environ("USERNAME")
On Error Resume Next
idxUser = Application.Match(user, Rows(1), 0)
On Error GoTo 0
Select Case idxUser
Case 0: seccao_type = "Not Found"
Case Else:
seccao_type = Cells(1, idxUser).Value2
Set rngUser = Cells(1, idxUser).Resize(20)
End Select
MsgBox seccao_type
End Sub
fgarcia90
07-11-2012, 02:56 PM
thanks to both, but as new in this... tried first GTO solution and worked perfectly.
and yes GTO i'm portuguese :)
Hi There,
I am glad we were able to help. Please note a tiny correction, as I missed spotting this in your original code:
Dim user As Variant
Dim seccao_type As String
'Dim tecnicos, comerciais As Range
Dim tecnicos As Range, comerciais As Range
When declaring a variable's Type, you must do this for each variable; otherwise, the variable defaults to a Variant type. Thus, in your original code (and the code I posted), 'tecnicos ' is actually a Variant rather than a Range as I'm sure you wanted it to be.
Mark
Aussiebear
07-12-2012, 02:28 AM
Good point made there Mark, as I've noticed a few posts lately where this has been occuring.
fgarcia90
07-12-2012, 07:59 AM
Thanks :)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.