hi Topper
that code is still giving me all the row names, but i have found a way to
program round that & just get only the references with both a row & col entry
input, so i am sorted.
thanks VERY much for your help with all this
jb
"Toppers" wrote:
Try ... I had ovelooked your TRUE/FALSE requirement:
If you still have problems mail sheet to me ( toppers at
REMOVETHISjohntopley.fsnet.co.uk).
Sub Find_Row2COLx()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim x As Variant
Dim findRng As Range
Dim r As Long, c As Integer, rr As Long
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set findRng = ws1.Range("i46:r62")
Findval = Application.InputBox("Enter value to be found", Type:=4)
rr = 49
With ws1
x = .Range("i46:r62")
For r = 1 To UBound(x, 1)
For c = 1 To UBound(x, 2)
If x(r, c) = Findval Then
rr = rr + 1
.Cells(rr, "T") = .Cells(r + 45, "F")
.Cells(rr, "U") = .Cells(7, c + 8)
End If
Next c
Next r
End With
End Sub
"JB2010" wrote:
hi Toppers
missed the fact that you had posted two different codes. my previous message
(about it listing rows twice), refers to the macro "Find_Row2COL" which
throws the answers to another sheet.
the macro "Find_Row2COLx" (that puts answers on the same sheet) does not
have the duplication problem but does still list all row results regardless.
hope this clears up any confusion
many thanks
jb
"JB2010" wrote:
Hi Toppers
The columns side of that works perfectly, but there seems to be a problem
with the row side.
the result brings up Sheet 2 col A listing all of the row headings twice
regardless of whether they are listed as TRUE or FALSE, but it only lists a
column heading in Sheet 2 col B where FALSE is actually found in the data
source. so it ends up looking like this;
A B
1 Row Col
2 Sales
3 Sales
4 Costs
5 Costs Mar
6 Total
7 Total
my vb is not good enough to spot what is working correctly on the column
aspects of the code that needs to be applied to the row aspects so the result
ends up looking like this;
A B
1 Row Col
2
3
4
5 Costs Mar
6
7
once again, really appreciate your help on this
jb