Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Impossible? Condensed search results
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Impossible? Condensed search results
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 -- If this post helps click Yes --------------- Jacob Skaria "LiAD" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Impossible? Condensed search results
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" wrote: 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 -- If this post helps click Yes --------------- Jacob Skaria "LiAD" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Impossible? Condensed search results
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 -- If this post helps click Yes --------------- Jacob Skaria "LiAD" wrote: 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" wrote: 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 -- If this post helps click Yes --------------- Jacob Skaria "LiAD" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Impossible? Condensed search results
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" wrote: 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 -- If this post helps click Yes --------------- Jacob Skaria "LiAD" wrote: 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" wrote: 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 -- If this post helps click Yes --------------- Jacob Skaria "LiAD" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Impossible? Condensed search results
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 --------------- Jacob Skaria "LiAD" wrote: 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" wrote: 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 -- If this post helps click Yes --------------- Jacob Skaria "LiAD" wrote: 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" wrote: 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 -- If this post helps click Yes --------------- Jacob Skaria "LiAD" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Impossible? Condensed search results
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" wrote: 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 --------------- Jacob Skaria "LiAD" wrote: 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" wrote: 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 -- If this post helps click Yes --------------- Jacob Skaria "LiAD" wrote: 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" wrote: 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 -- If this post helps click Yes --------------- Jacob Skaria "LiAD" wrote: 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Impossible? Condensed search results
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 --------------- Jacob Skaria |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Impossible? Condensed search results
Perfect.
Thanks "Jacob Skaria" wrote: 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 --------------- Jacob Skaria |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help! I can't get any search results | Excel Discussion (Misc queries) | |||
Search Results | Excel Discussion (Misc queries) | |||
Highlight Search Results | Excel Discussion (Misc queries) | |||
multiple results from search / how to? | Excel Discussion (Misc queries) | |||
Print the results of a search | Excel Discussion (Misc queries) |