How do I use a column to fill out another edited version of the first column

Question

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

#2

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

Posted 17-Apr-23 8:27am

Derek Duron

Updated 17-Apr-23 9:08am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2023-04-17T09:08:00

Solution 1

You mean something like that:

VB

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

Posted 17-Apr-23 9:08am

Maciej Los

Updated 17-Apr-23 9:50am

v3

Comments

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

Maciej Los 17-Apr-23 15:51pm

So, change the type of data for Y variable. It should be a string.

Derek Duron 17-Apr-23 15:59pm

Good now I get no errors! Thanks, One more thing tho. The strings are not shown in the H column, do I need to add something to write the values into the column?

Maciej Los 17-Apr-23 16:31pm

They should be there, because of this: wsh.Range("H" & i) = Y.