Click here to Skip to main content
15,789,776 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to get all rows of H2 to be equal to the edited rows of G2 as the code below describes but I can't figure out the proper for loop or another way of doing it
I tried #2 with bad results

What I have tried:

X = Range("G2")
Y = Mid(X, 9, 2) + Mid(X, 5, 3) + Right(X, 2)
Range("H2").value = Y


X = Range("G2:G200")
Y = Mid(X, 9, 2) + Mid(X, 5, 3) + Right(X, 2)
Range("H2:H200").value = Y
Updated 17-Apr-23 10:08am

1 solution

You mean something like that:
Dim wsh as Worksheet 
Dim X As String, Y As String
Dim i as Integer

Set wsh = ThiWorkbook.Worksheets("Sheet1")
For i = 2 to 200
  X = CStr(wsh.Range("G" & i))
  Y = Mid(X, 9, 2) & Mid(X, 5, 3) & Right(X, 2)    
  wsh.Range("H" & i) = Y
Next i
Set wsh = Nothing
Share this answer
Derek Duron 17-Apr-23 15:31pm    
Whenever I try the above I get the following error: Run-Time Error'13'. Type mismatch on line: Y = Mid(X, 9, 2) + Mid(X, 5, 3) + Right(X, 2)
Maciej Los 17-Apr-23 15:38pm    
Sorry, i forgot that VBA uses [&] to concatenate string, instead of [+].
Derek Duron 17-Apr-23 15:41pm    
I tried using both & and +, both of them give me the same error
Maciej Los 17-Apr-23 15:48pm    
What is the length of text stored in "G" & i?
BTW: Y should be a string variable :)
Derek Duron 17-Apr-23 15:50pm    
All strings in G have a length of 24

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900