Entering a value in one cell single or multiple times based on data present in another cel

[utking]

Hello,

I need help in entering a value in one cell single or multiple times based on data present in another cell separated by a line break.
For Example :

In the column - MPI ID-Type
I need to input the value '99020101' five times as there are five data values (separated by line break) in column - MPI ID.
Same needed for second row i.e.
I need to input the value '99020101' seven times as there are seven data values..

Screensho.png

Can anyone help me to achieve this as manually entering the data accordingly to another cell is very time consuming task.

Thanks & Best regards.

Attachment 803084

[Marc L]

Hello,

according to your attachment you just need this single VBA codeline : Range("A2", [B1].End(xlDown)(1, 0)).FillDown ...

[beyond Excel]

Hi utking,
No, no, no, no!... What is shown in the image has nothing to do with what is in your workbook. Specifically:

• In your picture, you has merged cells in column A.
• While in your workbook, column A does NOT have merged cells.

Could you clarify this?

[utking]

Thanks for pointing out this,

I don't know what the issue is but when I am opening the same file in google sheets it's been showing two columns but in MS-excel its showing merged.

I had check this and rectified the same.

[Marc L]

According to your new attachment an only Excel / VBA basics demonstration as a beginner starter :

Sub Demo1()
        Dim V, R&, C%
    With Range("B2", [B1].End(xlDown))
        V = .Value
    For R = 1 To .Rows.Count
        V(R, 1) = Split(V(R, 1), vbLf)
        For C = 0 To UBound(V(R, 1)) + (V(R, 1)(UBound(V(R, 1))) = ""):  V(R, 1)(C) = "99020101":  Next
        V(R, 1) = Join(V(R, 1), vbLf)
    Next
       .Columns(0).Font.Size = .Cells(1, 0).Font.Size
       .Columns(0) = V
    End With
End Sub 

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[Sintek]

@ MarcL

I notice that on all your posted code you always have undeclared variables...Just out of interest...Do you not make use of the Option Explicit - Force declaration setting...

[Marc L]

As when I code I merely do not need to declare any.
I just forgot here the variable C, my previous post is now updated, thanks ...

[Sintek]

Was just wondering as it would error when you test your code...

[Marc L]

No as this kind of code well works with only Variant variables like VBScript ...

So I test first my code then just before posting I add the Dim codeline 'cause of 'haters'
but here as I was on phone I just forgot the variable C.

[beyond Excel]

Hello utking. The macro that I show you below:

Please Login or Register  to view this content.

solves the following:

Sin t?tulo 1.gif

Cases:

• One, two, several or no data in column A.
• MPI ID-Type changes in column A

[Sintek]

Please Login or Register  to view this content.

[Marc L]

According to the attachment a variation of my previous demonstration for starters :

Sub Demo1v()
      Const C = "99020101", S = vbLf & C
        Dim V, R&
    With Range("B2", [B1].End(xlDown))
        V = .Value
    For R = 1 To .Rows.Count
        V(R, 1) = C & Application.Rept(S, Len(V(R, 1)) - Len(Replace(V(R, 1), vbLf, "")) + (Right(V(R, 1), 1) = vbLf))
    Next
       .Columns(0).Font.Size = .Cells(1, 0).Font.Size
       .Columns(0) = V
    End With
End Sub 

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[utking]

According to your new attachment an only Excel / VBA basics demonstration as a beginner starter :

Sub Demo1()
        Dim V, R&, C%
    With Range("B2", [B1].End(xlDown))
        V = .Value
    For R = 1 To .Rows.Count
        V(R, 1) = Split(V(R, 1), vbLf)
        For C = 0 To UBound(V(R, 1)) + (V(R, 1)(UBound(V(R, 1))) = ""):  V(R, 1)(C) = "99020101":  Next
        V(R, 1) = Join(V(R, 1), vbLf)
    Next
       .Columns(0).Font.Size = .Cells(1, 0).Font.Size
       .Columns(0) = V
    End With
End Sub 

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

The Code worked like a charm.
You are awesome. Thanks for the help sir.

Mods, Please close this thread.

[Marc L]

Thanks for the rep' !

You should better use my more direct post #12 variation code ...