Printable View
I've got certain percentage values going downwards in column J, with varying amounts of blank fields. I also have a similar situation in column W. I would like to write a macro that will scan through column J and W and then to output (in say column L) the difference between the first two values, 2nd values, etc.)
So for eg, if the first few values in column J (going downwards) were 10%,blank,blank,5%,1% and in column W 9%, 2%,blank, 3% , then the output in L should be 1%,3%, -2%
Thanks.
This UDF might help you.
pairDiff takes three arguments, two ranges and an index
pairDiff returns the difference between the (index)'th value in firstRange and the (index)'th value found in secondRange. It skips text and blanks, but counts dates and logical as numbers.
If there are fewer than (index) numeric entries in either of the ranges, it returns the #REF error.
Putting this in L1 and filling down should meet your need.
=pairDiff(J:J,W:W,ROW())
[VBA]Function pairDiff(ByRef firstRange As Range, ByRef secondRange As Range, ByVal index As Long) As Variant
Dim spareIndex As Long, xVal As Variant
Dim firstRRay As Variant, firstNum As Double
Dim secondRRay As Variant, secondNum As Double
Set firstRange = Application.Intersect(firstRange, firstRange.Parent.UsedRange)
If firstRange Is Nothing Then pairDiff = CVErr(xlErrNA): Exit Function
firstRRay = firstRange.Value
secondRRay = secondRange.Resize(firstRange.Rows.Count, firstRange.Columns.Count).Value
spareIndex = index
For Each xVal In firstRRay
If xVal <> vbNullString And IsNumeric(xVal) Then
firstNum = CDbl(xVal)
index = index - 1
If index = 0 Then Exit For
End If
Next xVal
If index <> 0 Then pairDiff = CVErr(xlErrRef): Exit Function
For Each xVal In secondRRay
If xVal <> vbNullString And IsNumeric(xVal) Then
secondNum = CDbl(xVal)
spareIndex = spareIndex - 1
If spareIndex = 0 Then Exit For
End If
Next xVal
If spareIndex <> 0 Then pairDiff = CVErr(xlErrRef): Exit Function
pairDiff = firstNum - secondNum
End Function
[/VBA]
I've a feeling there's a spreadsheet solution, but I can't find it.
Thanks heaps for that reply!
I have a different problem now, so i thought i'd keep it in this forum:
I've got two .xls files (call them form.xls and data.xls). In column B of form.xls, I need it to search in column P of data.xls where it will be looking for a certain time value, say 3:00:00 PM. When it finds this time value, it needs to fetch a number from column T of data.xls (let's say it was from T20). That number will then be inputted in cell B3 of form.xls. In addition, cell C3 of form.xls will take the value of the number in T19 of data.xls, D3(form) and T18(data), E3(form) and T17(data) etc, and so on up to K3 and T11. Then L3 will correspond with T6 (i.e. we skipped 5 from T11) and M3 with T(-13) *** [This is just to illustrate the relationship, negative rows don't exist].
The macro will keep searching for further instances of 3:00:00 PM and put successive matches in B4,B5, etc. whilst following the rules described previously (fetching the associated numbers, etc). To top it all off, when the macro finds the 3:00:00 PM value in column P of data.xls, it would be nice if it could also pull the date which is in the same row but in column O (of data.xls). Then the A column in form.xls would be populated with the matching date values.
So it boils down to searching one .xls for a certain date, fetching associated data and then pulling that data back into a different .xls
I'd appreciate any input or provide any further clarification. Thanks!!!
This doesn't make sense ...Quote:
Originally Posted by wakwak1
J W L (yes out of order)
10% 9% 1%
- 2% 3% (where from ?)
- - (0% or should this be blank ?)
5% 3% -2%
1% - (shouldn't -1% go here ?)
Where does the bold 3% come from ?
Rather than a macro you should use a formula and drag it down.
Why do you want to use a macro ?
No, you should start a new thread for each new problem.
I interpreted the question as
10 is the first non-blank in J, 1 is the first non-blank in W, 9 is the first result.
5 is the second non-blank in J, 2 is the second non-blank in W, result 3
1 is the third non-blank in J, 3 is the third non-blank in W, result -2.
Mike it obviously did what he wanted, so kudos to you, but that ain't what he said ! So, again, kudos to you for working it out. :)
thanks micerickson, that's what i was after. Sorry if it was a bit confusing.
I will post the other question in another thread, thanks everyone!