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 ?
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
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.