wilkisa
Well-known Member
- Joined
- Apr 7, 2002
- Messages
- 660
- Office Version
- 365
- 2016
- 2013
- Platform
- Windows
Our company PCs are locked down and I cannot install the HTML maker so this is the best I can do.
I am trying to create a macro that will find "Type III" in column B on the Data sheet. I then want it to return the remaining contents of the same cell after the word "Variable". Those contents are passed to a variable called ACTIVITY. The contents should then be copied/pasted to the Means Table sheet in the first cell of the table, which is cell B7. I then need to go back to the Data sheet and send the cursor down 5 rows and right 5 columns. I need it to copy the value in that cell and paste it in the Means Table in the first cell in column I in the table, I7. The macro needs to do this for every instance of "Type III" in the Data sheet, which has thousands of rows of data.
Below is the text of the macro I have started. I know I am way off because I don't want it to always use cell B87 in the Activity statement. I also don't know exactly how to loop.
Sub FindTypeIII()
'
' FindTypeIII Macro
'
Dim Activity As String
Dim PValue As Single
Sheets("Data").Activate
'Tell Excel what to find
Cells.Find(what:="Type III", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
'Extract the remaining text after the word "Variable"
Activity = Mid(Range("B87"), InStr(Range("B87"), "Variable") + 9, Len(Range("b87")) - InStr(Range("b87"), "Variable"))
Activity.Copy
Sheets("Means Table").Select
Set Activity = Sheets("Means Table").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
ActiveSheet.Paste
Would someone please give me a hand?
I am trying to create a macro that will find "Type III" in column B on the Data sheet. I then want it to return the remaining contents of the same cell after the word "Variable". Those contents are passed to a variable called ACTIVITY. The contents should then be copied/pasted to the Means Table sheet in the first cell of the table, which is cell B7. I then need to go back to the Data sheet and send the cursor down 5 rows and right 5 columns. I need it to copy the value in that cell and paste it in the Means Table in the first cell in column I in the table, I7. The macro needs to do this for every instance of "Type III" in the Data sheet, which has thousands of rows of data.
Below is the text of the macro I have started. I know I am way off because I don't want it to always use cell B87 in the Activity statement. I also don't know exactly how to loop.
Sub FindTypeIII()
'
' FindTypeIII Macro
'
Dim Activity As String
Dim PValue As Single
Sheets("Data").Activate
'Tell Excel what to find
Cells.Find(what:="Type III", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
'Extract the remaining text after the word "Variable"
Activity = Mid(Range("B87"), InStr(Range("B87"), "Variable") + 9, Len(Range("b87")) - InStr(Range("b87"), "Variable"))
Activity.Copy
Sheets("Means Table").Select
Set Activity = Sheets("Means Table").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
ActiveSheet.Paste
Would someone please give me a hand?