| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grouping values
Hi,
I have a sheet with various materials with different thickness. I want to group them by 0,03 - 0,02 - 0,01 and others. I wrote this: Sub Espessuras() Dim Esp As Integer 'Thickness article Dim Espessura As Integer 'Regroup thickness Dim linhaf As Integer Dim linha As Integer Range("A1").Select Selection.End(xlDown).Select linhaf = Selection.Row 'devolve o numero da linha For linha = 2 To linhaf 'inicio do ciclo Esp = Range("P" & linha).Value 'Espessura artigo descriminado Rows(linha & ":" & linha).Select ' selecção da linha If Esp = "0,03" Then Espessura = "0,03" ElseIf Esp = "0,02" Then Espessura = "0,02" ElseIf Esp = "0,01" Then Espessura = "0,01" ElseIf Esp < "0,03" And _ Esp < "0,02" And _ Esp < "0,01" Then Espessura = "1" End If Range("Q" & linha).Value = Espessura Next linha ' proxima linha End Sub **** Unfortunatly, it doesn't work! I give me only "1" as a result for all thickness no matter if they are 0,03, 0,02 or 0,01. Can someone help me on this one. Tkanks a lot. Barbara |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Grouping values
Hi Barbara,
Try something like: '============= Public Sub Espessuras() Dim Esp As Double 'Thickness article Dim Espessura As Double 'Regroup thickness Dim linhaf As Long Dim linha As Long linhaf = Range("A1").End(xlDown).Row For linha = 2 To linhaf Esp = Range("P" & linha).Value If Esp = "0.03" Then Espessura = "0.03" ElseIf Esp = "0.02" Then Espessura = "0.02" ElseIf Esp = "0.01" Then Espessura = "0.01" Else Espessura = "1" End If Range("Q" & linha).Value = Espessura Next linha End Sub '<<============= Note that VBA uses the US decimal separator rather than a comma. --- Regards, Norman "Barbara" wrote in message ... Hi, I have a sheet with various materials with different thickness. I want to group them by 0,03 - 0,02 - 0,01 and others. I wrote this: Sub Espessuras() Dim Esp As Integer 'Thickness article Dim Espessura As Integer 'Regroup thickness Dim linhaf As Integer Dim linha As Integer Range("A1").Select Selection.End(xlDown).Select linhaf = Selection.Row 'devolve o numero da linha For linha = 2 To linhaf 'inicio do ciclo Esp = Range("P" & linha).Value 'Espessura artigo descriminado Rows(linha & ":" & linha).Select ' selecção da linha If Esp = "0,03" Then Espessura = "0,03" ElseIf Esp = "0,02" Then Espessura = "0,02" ElseIf Esp = "0,01" Then Espessura = "0,01" ElseIf Esp < "0,03" And _ Esp < "0,02" And _ Esp < "0,01" Then Espessura = "1" End If Range("Q" & linha).Value = Espessura Next linha ' proxima linha End Sub **** Unfortunatly, it doesn't work! I give me only "1" as a result for all thickness no matter if they are 0,03, 0,02 or 0,01. Can someone help me on this one. Tkanks a lot. Barbara |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Grouping values
Thank you very much 4 your early reply. It works just fine as I didn't put
the " ". If Esp = 0.03 Then Espessura = 0.03 ElseIf Esp = 0.02 Then Espessura = 0.02 ElseIf Esp = 0.01 Then Espessura = 0.01 Else Espessura = 0 End If Now..... Is there a way to change this: Else Espessura = 0 with this: Else Espessura = "Others" I tryed but as it is text, it gives me an error! Thank you very much. Barbara "Norman Jones" wrote: Hi Barbara, Try something like: '============= Public Sub Espessuras() Dim Esp As Double 'Thickness article Dim Espessura As Double 'Regroup thickness Dim linhaf As Long Dim linha As Long linhaf = Range("A1").End(xlDown).Row For linha = 2 To linhaf Esp = Range("P" & linha).Value If Esp = "0.03" Then Espessura = "0.03" ElseIf Esp = "0.02" Then Espessura = "0.02" ElseIf Esp = "0.01" Then Espessura = "0.01" Else Espessura = "1" End If Range("Q" & linha).Value = Espessura Next linha End Sub '<<============= Note that VBA uses the US decimal separator rather than a comma. --- Regards, Norman "Barbara" wrote in message ... Hi, I have a sheet with various materials with different thickness. I want to group them by 0,03 - 0,02 - 0,01 and others. I wrote this: Sub Espessuras() Dim Esp As Integer 'Thickness article Dim Espessura As Integer 'Regroup thickness Dim linhaf As Integer Dim linha As Integer Range("A1").Select Selection.End(xlDown).Select linhaf = Selection.Row 'devolve o numero da linha For linha = 2 To linhaf 'inicio do ciclo Esp = Range("P" & linha).Value 'Espessura artigo descriminado Rows(linha & ":" & linha).Select ' selecção da linha If Esp = "0,03" Then Espessura = "0,03" ElseIf Esp = "0,02" Then Espessura = "0,02" ElseIf Esp = "0,01" Then Espessura = "0,01" ElseIf Esp < "0,03" And _ Esp < "0,02" And _ Esp < "0,01" Then Espessura = "1" End If Range("Q" & linha).Value = Espessura Next linha ' proxima linha End Sub **** Unfortunatly, it doesn't work! I give me only "1" as a result for all thickness no matter if they are 0,03, 0,02 or 0,01. Can someone help me on this one. Tkanks a lot. Barbara |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Grouping values
Hi Barbara,
'---------------- Now..... Is there a way to change this: Else Espessura = 0 with this: Else Espessura = "Others" I tryed but as it is text, it gives me an error! '---------------- Try declaring Espessura as variant: '============= Public Sub Espessuras() Dim Esp As Double 'Thickness article Dim Espessura As Variant 'Regroup thickness Dim linhaf As Long Dim linha As Long linhaf = Range("A1").End(xlDown).Row For linha = 2 To linhaf Esp = Range("P" & linha).Value If Esp = "0.03" Then Espessura = "0.03" ElseIf Esp = "0.02" Then Espessura = "0.02" ElseIf Esp = "0.01" Then Espessura = "0.01" Else Espessura = "Other" End If Range("Q" & linha).Value = Espessura Next linha End Sub '<<============= --- Regards, Norman |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Grouping values
Thank you so much! It works just as I wanted...
Have a nice day. Barbara PS:I love these Discussion Groups... They are so helpfull. "Norman Jones" wrote: Hi Barbara, '---------------- Now..... Is there a way to change this: Else Espessura = 0 with this: Else Espessura = "Others" I tryed but as it is text, it gives me an error! '---------------- Try declaring Espessura as variant: '============= Public Sub Espessuras() Dim Esp As Double 'Thickness article Dim Espessura As Variant 'Regroup thickness Dim linhaf As Long Dim linha As Long linhaf = Range("A1").End(xlDown).Row For linha = 2 To linhaf Esp = Range("P" & linha).Value If Esp = "0.03" Then Espessura = "0.03" ElseIf Esp = "0.02" Then Espessura = "0.02" ElseIf Esp = "0.01" Then Espessura = "0.01" Else Espessura = "Other" End If Range("Q" & linha).Value = Espessura Next linha End Sub '<<============= --- Regards, Norman |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula help...find values and grouping together... | Excel Discussion (Misc queries) | |||
| Grouping Corresponding Values | Excel Discussion (Misc queries) | |||
| Grouping Cell Values Together | Excel Discussion (Misc queries) | |||
| Help with grouping values | Excel Programming | |||
| Code for Grouping Text Values in Pivotfield | Excel Programming | |||
Linear Mode
