Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help with MACRO to do CRAZY thing,
Hello All,
First you guys are great with fast reponse! This time, I'd like you to help me with this crazy sorting, copy and paste. Example: ColA ColB ColC ....... ColJ Row 5 FileName SampleType SampleName Calculated Conc Row 6 080808-1 Blank Sample Test1 10 Row 7 080808-2 Standard Test2 15 Row 8 080808-3 Uknown Sample Test3 15 Here are what I need your help, 1/Row 5 is the headers as you can see 2/Sort (sample type) 3/Freezepanes at range("D6") 4/If col B contain the word (Unknown Sample), copy value in column J (Caculated conc) and put the sheet called RAWDATA. 5/Repeat other sheets (other sheets also have same exatly format). Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help with MACRO to do CRAZY thing,
Sub sortdata()
RawDataRow = 1 For Each sht In Sheets If UCase(sht.Name) < "RAWDATA" Then With sht 'Get Last row fo data LastRow = .Range("A" & Rows.Count).End(xlUp) .Rows("6:" & LastRow).Sort _ Key1:=.Range("C6"), _ Order1:=xlAscending, _ Header:=xlNo Set SearchRange = .Range("B6:B" & LastRow) For Each cell In SearchRange If UCase(Cells.Value) = "UNKNOWN SAMPLE" Then Sheets("RAWDATA").Range("A" & RawDataRow) = _ .Range("J" & cell.Row) RawDataRow = RawDataRow + 1 End If Next cell End With End If Next sht End Sub "bioyyy" wrote: Hello All, First you guys are great with fast reponse! This time, I'd like you to help me with this crazy sorting, copy and paste. Example: ColA ColB ColC ....... ColJ Row 5 FileName SampleType SampleName Calculated Conc Row 6 080808-1 Blank Sample Test1 10 Row 7 080808-2 Standard Test2 15 Row 8 080808-3 Uknown Sample Test3 15 Here are what I need your help, 1/Row 5 is the headers as you can see 2/Sort (sample type) 3/Freezepanes at range("D6") 4/If col B contain the word (Unknown Sample), copy value in column J (Caculated conc) and put the sheet called RAWDATA. 5/Repeat other sheets (other sheets also have same exatly format). Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help with MACRO to do CRAZY thing,
Joel:
Thanks for code. However, I got an error (Run-time error '13': Type mismatch). Thanks, "Joel" wrote: Sub sortdata() RawDataRow = 1 For Each sht In Sheets If UCase(sht.Name) < "RAWDATA" Then With sht 'Get Last row fo data LastRow = .Range("A" & Rows.Count).End(xlUp) .Rows("6:" & LastRow).Sort _ Key1:=.Range("C6"), _ Order1:=xlAscending, _ Header:=xlNo Set SearchRange = .Range("B6:B" & LastRow) For Each cell In SearchRange If UCase(Cells.Value) = "UNKNOWN SAMPLE" Then Sheets("RAWDATA").Range("A" & RawDataRow) = _ .Range("J" & cell.Row) RawDataRow = RawDataRow + 1 End If Next cell End With End If Next sht End Sub "bioyyy" wrote: Hello All, First you guys are great with fast reponse! This time, I'd like you to help me with this crazy sorting, copy and paste. Example: ColA ColB ColC ....... ColJ Row 5 FileName SampleType SampleName Calculated Conc Row 6 080808-1 Blank Sample Test1 10 Row 7 080808-2 Standard Test2 15 Row 8 080808-3 Uknown Sample Test3 15 Here are what I need your help, 1/Row 5 is the headers as you can see 2/Sort (sample type) 3/Freezepanes at range("D6") 4/If col B contain the word (Unknown Sample), copy value in column J (Caculated conc) and put the sheet called RAWDATA. 5/Repeat other sheets (other sheets also have same exatly format). Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help with MACRO to do CRAZY thing,
Try changing:
LastRow = .Range("A" & Rows.Count).End(xlUp) to LastRow = .Range("A" & Rows.Count).End(xlUp).Row bioyyy wrote: Joel: Thanks for code. However, I got an error (Run-time error '13': Type mismatch). Thanks, "Joel" wrote: Sub sortdata() RawDataRow = 1 For Each sht In Sheets If UCase(sht.Name) < "RAWDATA" Then With sht 'Get Last row fo data LastRow = .Range("A" & Rows.Count).End(xlUp) .Rows("6:" & LastRow).Sort _ Key1:=.Range("C6"), _ Order1:=xlAscending, _ Header:=xlNo Set SearchRange = .Range("B6:B" & LastRow) For Each cell In SearchRange If UCase(Cells.Value) = "UNKNOWN SAMPLE" Then Sheets("RAWDATA").Range("A" & RawDataRow) = _ .Range("J" & cell.Row) RawDataRow = RawDataRow + 1 End If Next cell End With End If Next sht End Sub "bioyyy" wrote: Hello All, First you guys are great with fast reponse! This time, I'd like you to help me with this crazy sorting, copy and paste. Example: ColA ColB ColC ....... ColJ Row 5 FileName SampleType SampleName Calculated Conc Row 6 080808-1 Blank Sample Test1 10 Row 7 080808-2 Standard Test2 15 Row 8 080808-3 Uknown Sample Test3 15 Here are what I need your help, 1/Row 5 is the headers as you can see 2/Sort (sample type) 3/Freezepanes at range("D6") 4/If col B contain the word (Unknown Sample), copy value in column J (Caculated conc) and put the sheet called RAWDATA. 5/Repeat other sheets (other sheets also have same exatly format). Thanks -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help with MACRO to do CRAZY thing,
Dave,
Thanks. That was fixed. However there is an error for If UCase(Cells.Value) = "UNKNOWN SAMPLE" Then (Run-time error '13': Type mismatch) Thanks, "Dave Peterson" wrote: Try changing: LastRow = .Range("A" & Rows.Count).End(xlUp) to LastRow = .Range("A" & Rows.Count).End(xlUp).Row bioyyy wrote: Joel: Thanks for code. However, I got an error (Run-time error '13': Type mismatch). Thanks, "Joel" wrote: Sub sortdata() RawDataRow = 1 For Each sht In Sheets If UCase(sht.Name) < "RAWDATA" Then With sht 'Get Last row fo data LastRow = .Range("A" & Rows.Count).End(xlUp) .Rows("6:" & LastRow).Sort _ Key1:=.Range("C6"), _ Order1:=xlAscending, _ Header:=xlNo Set SearchRange = .Range("B6:B" & LastRow) For Each cell In SearchRange If UCase(Cells.Value) = "UNKNOWN SAMPLE" Then Sheets("RAWDATA").Range("A" & RawDataRow) = _ .Range("J" & cell.Row) RawDataRow = RawDataRow + 1 End If Next cell End With End If Next sht End Sub "bioyyy" wrote: Hello All, First you guys are great with fast reponse! This time, I'd like you to help me with this crazy sorting, copy and paste. Example: ColA ColB ColC ....... ColJ Row 5 FileName SampleType SampleName Calculated Conc Row 6 080808-1 Blank Sample Test1 10 Row 7 080808-2 Standard Test2 15 Row 8 080808-3 Uknown Sample Test3 15 Here are what I need your help, 1/Row 5 is the headers as you can see 2/Sort (sample type) 3/Freezepanes at range("D6") 4/If col B contain the word (Unknown Sample), copy value in column J (Caculated conc) and put the sheet called RAWDATA. 5/Repeat other sheets (other sheets also have same exatly format). Thanks -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help with MACRO to do CRAZY thing,
There was a typo.
I'd use this (untested, but compiled): Option Explicit Sub sortdata() Dim RawDataRow As Long Dim LastRow As Long Dim Sht As Worksheet Dim SearchRange As Range Dim Cell As Range RawDataRow = 1 For Each Sht In Worksheets If UCase(Sht.Name) < "RAWDATA" Then With Sht 'Get Last row fo data LastRow = .Range("A" & Rows.Count).End(xlUp).Row .Rows("6:" & LastRow).Sort _ Key1:=.Range("C6"), _ Order1:=xlAscending, _ Header:=xlNo Set SearchRange = .Range("B6:B" & LastRow) For Each Cell In SearchRange If UCase(Cells.Value) = "UNKNOWN SAMPLE" Then Sheets("RAWDATA").Range("A" & RawDataRow) = _ .Range("J" & Cell.Row) RawDataRow = RawDataRow + 1 End If Next Cell End With End If Next Sht End Sub bioyyy wrote: Dave, Thanks. That was fixed. However there is an error for If UCase(Cells.Value) = "UNKNOWN SAMPLE" Then (Run-time error '13': Type mismatch) Thanks, "Dave Peterson" wrote: Try changing: LastRow = .Range("A" & Rows.Count).End(xlUp) to LastRow = .Range("A" & Rows.Count).End(xlUp).Row bioyyy wrote: Joel: Thanks for code. However, I got an error (Run-time error '13': Type mismatch). Thanks, "Joel" wrote: Sub sortdata() RawDataRow = 1 For Each sht In Sheets If UCase(sht.Name) < "RAWDATA" Then With sht 'Get Last row fo data LastRow = .Range("A" & Rows.Count).End(xlUp) .Rows("6:" & LastRow).Sort _ Key1:=.Range("C6"), _ Order1:=xlAscending, _ Header:=xlNo Set SearchRange = .Range("B6:B" & LastRow) For Each cell In SearchRange If UCase(Cells.Value) = "UNKNOWN SAMPLE" Then Sheets("RAWDATA").Range("A" & RawDataRow) = _ .Range("J" & cell.Row) RawDataRow = RawDataRow + 1 End If Next cell End With End If Next sht End Sub "bioyyy" wrote: Hello All, First you guys are great with fast reponse! This time, I'd like you to help me with this crazy sorting, copy and paste. Example: ColA ColB ColC ....... ColJ Row 5 FileName SampleType SampleName Calculated Conc Row 6 080808-1 Blank Sample Test1 10 Row 7 080808-2 Standard Test2 15 Row 8 080808-3 Uknown Sample Test3 15 Here are what I need your help, 1/Row 5 is the headers as you can see 2/Sort (sample type) 3/Freezepanes at range("D6") 4/If col B contain the word (Unknown Sample), copy value in column J (Caculated conc) and put the sheet called RAWDATA. 5/Repeat other sheets (other sheets also have same exatly format). Thanks -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Crazy If | Excel Worksheet Functions | |||
HEEEEEEEEEELP!! This thing is driving me crazy. | Excel Discussion (Misc queries) | |||
I'm going crazy here | Excel Discussion (Misc queries) | |||
Macro thing? | Excel Worksheet Functions | |||
This is crazy!!! | Excel Discussion (Misc queries) |