NightOwlAnna
03-13-2022, 10:52 AM
I'm a VBA newby in need of some help.
I want to:
Step 1:
Copy column D from D2 to the cell in column D where there is the last cell with text
Paste this to column F from F2 onwards
Step 2:
Copy 2 columns (A and B to wherever there is the last cell with text) from document called: DO NOT TOUCH as it is used for MarketTitle VBA.xlsx Which is in my sharepoint and not located on the actual internal memory of the person using the code.
Paste this to Column H and I (where info from A needs to be in H and from B to I)
Step 3:
Use the information in Column H and I to do a find replace with multiple values for Column F (from F2 onwards until last cell with text). Where info in column H is original information and needs to be replaced with what's in column I
Step 4:
Remove any information in column H and I
----
I think I might be able to optimise the process a bit to combine step 2 to 4. I just need the information in the document mentioned to serve as data for find and replace with multiple values. That said, I don't really know what I'm doing if I'm honest. I've got exactly zero experience with VBA.
I appreciate any help to fix my very shitty code. This is how far I've got. I'm not sure how ugly it looks, but I know there's a lot that still needs fixing. I tried to be as clear as possible and mention everything involved, but if you need more info to fix it, just let me know. I appreciate any help and I hope I gave you all a nice little puzzle (but I suspect it's rather simple).
Sub TranslateMarketTitle()
'Step1
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("F2").Select
ActiveSheet.Paste
' Step 2-4?
Windows("DO NOT TOUCH as it is used for MarketTitle VBA.xlsx").Activate
Range("A2:B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("NAMEOFYOURDOCUMENT").Activate
Range("H1").Select
ActiveSheet.Paste
Dim Rng As Range
Dim InputRng As Range, ReplaceRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8)
'SOME CODE THAT FILLS IN THE INPUTBOX
Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8)
'SOME CODE THAT FILLS IN THE OTHER INPUTBOX
Application.ScreenUpdating = False
For Each Rng In ReplaceRng.Columns(1).Cells
InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value
Next
Application.ScreenUpdating = True
'SOME CODE THAT REMOVES INFO IN COLUMN H AND I
End Sub
I appreciate all the help I can get! I'm here to learn and seems I'm learning as I go.
I want to:
Step 1:
Copy column D from D2 to the cell in column D where there is the last cell with text
Paste this to column F from F2 onwards
Step 2:
Copy 2 columns (A and B to wherever there is the last cell with text) from document called: DO NOT TOUCH as it is used for MarketTitle VBA.xlsx Which is in my sharepoint and not located on the actual internal memory of the person using the code.
Paste this to Column H and I (where info from A needs to be in H and from B to I)
Step 3:
Use the information in Column H and I to do a find replace with multiple values for Column F (from F2 onwards until last cell with text). Where info in column H is original information and needs to be replaced with what's in column I
Step 4:
Remove any information in column H and I
----
I think I might be able to optimise the process a bit to combine step 2 to 4. I just need the information in the document mentioned to serve as data for find and replace with multiple values. That said, I don't really know what I'm doing if I'm honest. I've got exactly zero experience with VBA.
I appreciate any help to fix my very shitty code. This is how far I've got. I'm not sure how ugly it looks, but I know there's a lot that still needs fixing. I tried to be as clear as possible and mention everything involved, but if you need more info to fix it, just let me know. I appreciate any help and I hope I gave you all a nice little puzzle (but I suspect it's rather simple).
Sub TranslateMarketTitle()
'Step1
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("F2").Select
ActiveSheet.Paste
' Step 2-4?
Windows("DO NOT TOUCH as it is used for MarketTitle VBA.xlsx").Activate
Range("A2:B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("NAMEOFYOURDOCUMENT").Activate
Range("H1").Select
ActiveSheet.Paste
Dim Rng As Range
Dim InputRng As Range, ReplaceRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8)
'SOME CODE THAT FILLS IN THE INPUTBOX
Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8)
'SOME CODE THAT FILLS IN THE OTHER INPUTBOX
Application.ScreenUpdating = False
For Each Rng In ReplaceRng.Columns(1).Cells
InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value
Next
Application.ScreenUpdating = True
'SOME CODE THAT REMOVES INFO IN COLUMN H AND I
End Sub
I appreciate all the help I can get! I'm here to learn and seems I'm learning as I go.