Impossible? Condensed search results

[LiAD]

Good morning,

I have previously tried posting this but apparently the solution is quite
complicated. The result in the end gives quite a long winded table that then
needs filtered and whatever.

Does anyone else have any ideas?
-----------------------------

I have a list of dt 7a (mixture of text and numbers) arranged in a
horizontal column such as below, (for ref 563 ET 761 is the contents of one
cell, all fields below are single cells, just some have spaces).

Row 10 inputs are --- 0 0 563 ET 761 2
7 5N F0,035
Row 11 inputs are -- 4N F6 2 10 25 CU 3 4 ET
7 12

The contents of the non numeric cells are completely changeable between
different characters, numbers, spaces and position in the horizontal row.

I would like to output, in adjacent cells (cols A, B, C for example) in row
12 and 13 just the non numeric data.

Row 12 - 563 ET 761 5N F0,0035
Row 13 - 4N F6 25 CU 3 4 ET 7

There are no only numerical cells that i need to output, just the cells that
contain mixed text and numbers.

Does anyone know the simplest way to create this output?

Thanks

 

[Jacob Skaria]

Use the below procedure to accomplish this

If you want to transfer the special contents from row 11 to row 14 then from
your macro call this procedure like.....

CopySpecialCells 11, 14

If you want to repeat this for a range of rows then use loop

For lngRow = 10 to 11
CopySpecialCells lngRow, lngRow +10
Next


Sub CopySpecialCells(lngSourceRow As Long, lngTargetRow As Long)
lngTemp = 1
For lngCol = 1 To 7
If IsNumeric(Cells(lngSourceRow, lngCol)) = False Then
Cells(lngTargetRow, lngTemp) = Cells(lngSourceRow, lngCol)
lngTemp = lngTemp + 1
End If
Next
End Sub

 

[LiAD]

Sorry but I just realised I didnt update the post. The ranges I gave infact
are not the ones I'm actually using, I simplified the question in thinking
that I formula would be possible.

I'm not too clued in on programming so its starting to get a bit past me.

My actual data is about 100 rows long, starting in col AU237 going to col
DJ337 from which I need to pick up the cells that are mixed text and numbers
as per the previous post.

is this still possible with your code?

Thanks and sorry for the confusing shortcuts that I forgot to amend.

 

[Jacob Skaria]

If you are new to macros, set the Security level to low/medium in
(Tools|Macro|Security). 'Launch VBE using short-key Alt+F11. Insert a module
and paste the below code. Save. Get back to Workbook. Tools|Macro|Run Macro1()

Macro1()
'AU to DJ
For lngRow = 237 to 337
CopySpecialCells lngRow, lngRow + 101
Next
End Sub

Sub CopySpecialCells(lngSourceRow As Long, lngTargetRow As Long)
lngTemp = 47
For lngCol = 47 To 114
If IsNumeric(Cells(lngSourceRow, lngCol)) = False Then
Cells(lngTargetRow, lngTemp) = Cells(lngSourceRow, lngCol)
lngTemp = lngTemp + 1
End If
Next
End Sub

 

[LiAD]

Done as requested.

In order to get Macro 1 to appear in the macro run menu I wrote Sub Macro
1() instead of Macro 1(). If I try to run without the sub i can't find it to
run it.

When i run the macro i get a compile error - 'ByRef argument type mismatch'
and it highlights the Ing of the 4th row, (pasted below).

CopySpecialCells lngRow, lngRow + 101

Is there something I am doing wrong?
I was just thinking as well where will this create the output? If I know
then I can change it to wherever I need for different sheets etc.

Thanks a million and sorry for all the questions

 

[Jacob Skaria]

Sorry. I didnt test it earlier. Now this should work

Sub Macro1()
'AU to DJ
Dim lngRow As Long
For lngRow = 237 To 337
Call CopySpecialCells(lngRow, lngRow + 101)
Next
End Sub

Sub CopySpecialCells(lngSourceRow As Long, lngTargetRow As Long)
lngTemp = 47
For lngCol = 47 To 114
If IsNumeric(Cells(lngSourceRow, lngCol)) = False Then
Cells(lngTargetRow, lngTemp) = Cells(lngSourceRow, lngCol)
lngTemp = lngTemp + 1
End If
Next
End Sub


If this post helps click Yes

 

[LiAD]

super seems to work fine.

Last question - where does it put the output? I can't find it. If I know
what to change then I can alter as needed, at the moment i'll put the first
row in AA3.

Thanks a million, saved me a serious amount of head scratching!!

 

[Jacob Skaria]

The output is from row AU 338 as mentioned in the below code +101
Call CopySpecialCells(lngRow, lngRow + 101)

If this post helps click Yes

 

[LiAD]

Perfect.

Thanks