How to pass the same text to specified no.of cells in a column through VBA

Question

1.00/5 (1 vote)

See more:

Hi,

I have my data as below.

input:
Loc_id name
0 hyd
HYD001 hyd
0 hyd
0 hyd
Abc.a ban
0 ban
0 ban
Ram pune
0 xyz
0 ban
Loc_id name
0 hyd
HYD002 hyd
0 hyd
0 hyd
Abc.a ban
0 ban
0 ban
Ram pune
0 xyz
0 ban

I need to see data as below format

C#

Required Op:
	
loc_id	name
HYD001	hyd
HYD001	hyd
HYD001	hyd
HYD001	hyd
HYD001	ban
HYD001	ban
HYD001	ban
HYD001	pune
HYD001	xyz
HYD001	ban
HYD002	hyd
HYD002	hyd
HYD002	hyd
HYD002	hyd
HYD002	ban
HYD002	ban
HYD002	ban
HYD002	pune
HYD002	xyz
HYD002	ban

Thanks in Advance.

What I have tried:

I have tried with below code.But this code is working statically.But my requirement is to change data Dynamically

Sub REPLACE()

Worksheets("Sheet1").Columns("A").REPLACE _
What:="Abc.a", Replacement:="HYD001", _
SearchOrder:=xlByColumns, MatchCase:=True

End Sub

Posted 12-Sep-16 2:24am

Member 12726897

Updated 12-Sep-16 2:31am

v2

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 · Answer 1 · 2016-09-12T02:31:00

Check this: http://www.codeproject.com/Questions/1126932/How-to-delete-sub-header-columns-in-excel-VBA#answer2[^]

Based on above you were able to change the code to your needs:

VB

Sub Cleaning3()
    Dim wsh As Worksheet
    Dim i As Integer ', idcounter As Integer
    'context
    Set wsh = ThisWorkbook.Worksheets(1)
    '
    'idcounter = 1
    'starting row
    i = 2
    'till cell in col. A is not empty
    Do While wsh.Range("A" & i) <> ""
        'if zero - get value from below cell
        If wsh.Range("A" & i) = 0 Then
            wsh.Range("A" & i) = wsh.Range("A" & i).Offset(RowOffset:=1)
        End If
        'if cell contains non-numeric value and it's not a "id" - get value from above cell
        If LCase(wsh.Range("A" & i)) <> "loc_id" And Not wsh.Range("A" & i) Like UCase(wsh.Range("B2")) & "*" Then
            wsh.Range("A" & i) = wsh.Range("A" & i).Offset(RowOffset:=-1)
        End If
        'if "id" - remove entire row
        If LCase(wsh.Range("A" & i)) = "loc_id" Then
            wsh.Range("A" & i).EntireRow.Delete xlShiftUp
            i = i - 1
        End If
        i = i + 1
    Loop
 
End Sub