PDA

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

GTO
07-10-2012, 11:08 PM
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 :)

GTO
07-11-2012, 09:01 PM
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 :)