Filter A Range And Chart It

[rodolfon]

Hi, People

If anyone can give me a help I would greatly appreciate it!

I am developing a simple macro that will filter the content of some columns, you will get the address of the desired cell, select those cells, and then it will plot on a graph!

In fact, the macro will end a little more complex, but I'm developing this initially and then refine the process as well:

Initially I have some data ranging from B4 to something, I have to check which cells in this range have a value (in the example I'm using 0.5) when I find that value, I store the ordered pairs (Xi, Yi), where "i" is the line number where I am! Everything works beauty in getting the address, the problem is to select the Range!

I will post some of the code here to stay understanding easier:



Sub Teste()
'Declaração de variáveis
Dim celula As Range
Dim minvalue As Double
Dim enderecos As String
Dim enderecosunion As String

'Determinação do valor das variáveis
minvalue = 0.5

'Verificacao dos Filtros e obtenção dos endereços
For Each celula In Range("B4:" & ActiveSheet.Range("B65536").End(xlUp).Address).Cells
If celula.Value = minvalue Then
enderecos = enderecos & "," & celula.Offset(0,1).Address(False, False) & _
"," & celula.Offset(0, 4).Address(False, False)
End If
Next
enderecos = Right(enderecos, Len(enderecos) - 1)
Range(enderecos).Select
End Sub


Everything works fine, however, when selecting the cells obtained, I get the following error:

Run-time error '1004'
Method 'Range' of object '_Global' failed


I tried using the union function, however, the interval obtained does not work to plot the graph!
Does anyone know how to solve this?

thank you so much

[SamT]

For Each celula In Range(Range("B4"), Cells(Rows.Count,"B").End(xlUp))

Since I do not know your final purpose, I can not comment on the rest of your code.

Note the comma in Range(Range("B4"), Cells(Rows.Count,"B").End(xlUp)) and in enderecos & "," & celula. You can not expect different result with the same form.

[rodolfon]

For Each celula In Range(Range("B4"), Cells(Rows.Count,"B").End(xlUp))

Since I do not know your final purpose, I can not comment on the rest of your code.

Note the comma in Range(Range("B4"), Cells(Rows.Count,"B").End(xlUp)) and in enderecos & "," & celula. You can not expect different result with the same form.

Hello, thanks for the reply!
I believe that the problem is not the loop, because the addresses of the cells are being successfully achieved, the problem is the time I'll select the cells!
Here is an example file, if they can take a look I would appreciate it!
16376

[SamT]

The problem is in Range(enderecos).Select The different addresses in enderecos are separated by commas.

In my Range(Range("B4"), Cells(Rows.Count,"B").End(xlUp)) the comma means the entire Range from B4 to the bottom of the used range of column "B"

:dunno Try separating the addresses with a semicolon. :dunno

Since I do not know your final purpose, I can not comment on your use of "Select". If you are just testing, it is OK.

[rodolfon]

Hello, I did your way, but the error persists ... what my purpose end is to make a scatter plot with the coordinates found, However, in the chart gives the same error which gives when trying to select the cells

[SamT]

Can you use the values directly rather than the cell references?

[rodolfon]

Can you use the values directly rather than the cell references?

Yes, I can, but do not know how to do this in Excel, I can only add values that are in a cell! :(

[mancubus]

with the current file,
enderecos = B4,C4,B5,C5,B6,C6,B7,C7,B8,C8,B9,C9,B10,C10,B11,C11,B12,C12,B13,C13,B14,C14 ,B15,C15,B16,C16,B17,C17,B18,C18,B19,C19,
B20,C20,B21,C21,B22,C22,B23,C23,B26,C26,B27,C27,B29,C29,B30,C30,B31,C31,B32 ,C32,B33,C33,B34,C34,B35,C35,B36,C36,B37,C37,B38,C38,
B39,C39,B40,C40,B41,C41,B42,C42,B43,C43,B44,C44,B45,C45,B46,C46,B47,C47,B48 ,C48,B51,C51,B52,C52,B53,C53

its lengt is 347 characters which exceeds the 255 character limit when evauating to a range.

[SamT]

I think that I have used the Chart Wizard twice, but that is all I know about charting.

Let us rearrange the problem.


Sub GettingRanges()
Dim AreaDoGráfico as Range
AreaDoGráfico = Range(Cells(4, "B"), Cells(Rows.Count, "C").End(xlUp))
MsgBox AreaDoGráfico.Address
End Sub


Sub RangeToArray()
Dim arMatriz As Variant 'Prefix "ar" for "Array"
Dim AreaDoGráfico as Range
Dim i As Long

AreaDoGráfico = Range(Cells(4, "B"), Cells(Rows.Count, "C").End(xlUp))

arMatriz = AreaDoGráfico

For i = LBound(arMatriz ) To UBound(arMatriz)
MsgBox arMatriz(i, 1)
MsgBox arMatriz(i, 2)
Next
End Sub



Sub Oneline()
Dim arMatrizAs Variant
arMatriz= Range(Cells(4, "B"), Cells(Rows.Count, "C").End(xlUp))
End Sub


Perhaps you can filter Range("B:C") before you set AreaDoGráfico to SpecialCells
Perhaps you can Test the values in arMatriz and add the to the Chart one at a time.
Perhaps someone who knows Charts will see this and help you more than I can.
I will change the Thread Title to attract those people

[mdmackillop]

As Sam says, try a filter procedure

Sub Teste()
Dim celula As Range
Dim minvalue As Double
Dim enderecos As String
Dim enderecosunion As String
Dim r As Range

minvalue = 0.5

Range("B:B").AutoFilter 1, minvalue
Set r = Range("B:C").SpecialCells(xlCellTypeVisible)
Range("B:B").AutoFilter
r.Select
End Sub

[mdmackillop]

Range(Range("B4"), Cells(Rows.Count,"B").End(xlUp))

Use

Range(Cells(4,"B"), Cells(Rows.Count,"B").End(xlUp))