PDA

View Full Version : vba



Mergh06
09-24-2011, 11:11 AM
hi


hello: i have alot of values
i need to find ONLY 40-70 values our of alot of value ... how do i do that ?

and if you find this, how do you show "the row and column" for this cell ?

GTO
09-24-2011, 11:46 AM
Greetings Mergh06,

Let me be the first to welcome you to vbaexpress :-)

Are you saying that you are only looking for 40 to 70 values amongst many values, or are you looking to find all values that are between 40 and 70?

Mark

Paul_Hossler
09-24-2011, 12:11 PM
Also,

1. what do you want to do if any of the 40-70 occur more than once?

2. where to want to put or what do you want to do with the row & col?

Paul

Mergh06
09-24-2011, 10:37 PM
greetings, and thanks for the welcome!

let me give an example:

a b c d e

1 39 0 0 0 0
2 39 0 0 0 0
3 39 0 0 0 0
4 49 0 0 0 0
5 59 0 0 0 0

then the code should give me this outcome:
a4 and a5 (cause they are between 40 - 70)

I have alot more data, and i need to find specific values...

Mergh

GTO
09-24-2011, 11:07 PM
Hi Mergh,

Plase answer Paul's questions. It may make things much clearer for us, if you put together a small workbook (.xls format) with some sample data in BEFORE and AFTER layout.

To attach a wb, use the <Go Advanced> button below the quick reply box. In the new window, scroll down and press the <Manage Attachments> button.

Mark

Mergh06
09-24-2011, 11:28 PM
1. what do you want to do if any of the 40-70 occur more than once?

I wonna know which columns & rows for these values. It will happen more than once, but i just need to know the placement of these values...


2. where to want to put or what do you want to do with the row & col?

If the value 45 accure for number 5(row 7) and labor (coloum E) i need to have the following result in a result sheet:


5 Labor E7




Martin

Bob Phillips
09-25-2011, 03:23 AM
Public Sub ProcessData()
Dim lastrow As Long
Dim lastcol As Long
Dim nextrow As Long
Dim i As Long, ii As Long

Application.ScreenUpdating = False

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For i = 3 To lastrow

For ii = 2 To lastcol

If .Cells(i, ii).Value > 40 And .Cells(i, ii).Value < 70 Then

nextrow = nextrow + 1
Worksheets("Sheet2").Cells(nextrow, "A").Value = .Cells(1, ii).Value2
Worksheets("Sheet2").Cells(nextrow, "B").Value = .Cells(i, ii).Address(False, False)
End If
Next ii
Next i
End With

Application.ScreenUpdating = True
End Sub

Mergh06
09-25-2011, 03:25 AM
Thanks alot !

Mergh06
09-25-2011, 03:39 AM
The data look like this (in the new attached document)

How do you do the count thing then?

Thank you
Mergh

Mergh06
09-25-2011, 03:42 AM
here it is:)

Bob Phillips
09-25-2011, 03:43 AM
Your example was bad, you put in a number that was not between 40 and 70, I thought it was just the row number.



Public Sub ProcessData()
Dim lastrow As Long
Dim lastcol As Long
Dim nextrow As Long
Dim i As Long, ii As Long

Application.ScreenUpdating = False

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For i = 3 To lastrow

For ii = 2 To lastcol

If .Cells(i, ii).Value > 40 And .Cells(i, ii).Value < 70 Then

nextrow = nextrow + 1
Worksheets("Sheet2").Cells(nextrow, "A").Value = .Cells(i, ii).Value2
Worksheets("Sheet2").Cells(nextrow, "B").Value = .Cells(1, ii).Value2
Worksheets("Sheet2").Cells(nextrow, "C").Value = .Cells(i, ii).Address(False, False)
End If
Next ii
Next i
End With

Application.ScreenUpdating = True
End Sub

Mergh06
09-25-2011, 03:53 AM
I just need to find the values.. But if i add new values, then I need to have the oppotunity to get more new information... how should I deal with that? (in the new excel)

Mergh06
09-25-2011, 04:04 AM
ah its great, thanks

Mergh06
09-25-2011, 04:13 AM
can it be done so it highlight the 40-70 values, in the same sheet? (sheet1)
Maybe make a orange filled color ?

Bob Phillips
09-25-2011, 04:47 AM
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 Then
If Target.Value > 40 And Target.Value < 70 Then

Target.Interior.ColorIndex = 44
Else

Target.Interior.ColorIndex = xlColorIndexNone
End If
End If
End Sub

Mergh06
09-25-2011, 04:56 AM
Hi again, I dont think its working this one.. can be fixed?:)

But thanks alot until now!

Mergh

Mergh06
09-25-2011, 04:59 AM
I can see that it will get filled if I write a number between 40-70.. thats good, but if the numbers between 40-70 could be filled from the beginning it would be better:)

Bob Phillips
09-25-2011, 06:44 AM
Just edit them all.

Bob Phillips
09-25-2011, 06:44 AM
Or write a small macro to read the cells and write them back.