Fill Adjacent Columns based on cell value

[AriffChowdhury]

Hello,
I have string in column A.
I input some numerical values (null -15) in column B .
I want vba / function to look for the value entered in B and based on this value fill the adjcaent columns with string in column A by adding "_C" & "number" with the string in column A

For example;
I have string in cell A1 "volume1".
I input value 3 into cell B1.
I want concatenate the string of column A with "_C" & "number", and fill the adjcent no. of column based on the value.
I want "volume1_C1" in column C1, "volume1_C2" in D1 and "volume1_C3 in E1

[TABLE="width: 723"]
<colgroup><col style="text-align: center;"><col style="text-align: center;"><col span="5" style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]Headers[/TD]
[TD="align: center"]Values[/TD]
[TD="align: center"]Header1[/TD]
[TD="align: center"] Header2[/TD]
[TD="align: center"] Header3 [/TD]
[TD="align: center"]Header4[/TD]
[TD="align: center"] Header5[/TD]
[TD="align: center"] Header6[/TD]
[/TR]
[TR]
[TD="align: center"]Volume1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Volume1_C1[/TD]
[TD="align: center"]Volume1_C2[/TD]
[TD="align: center"]Volume1_C3[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]Volume2[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]Volume3[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]Volume3_C1[/TD]
[TD="align: center"]Volume3_C2[/TD]
[TD="align: center"]Volume3_C3[/TD]
[TD="align: center"]Volume3_C4[/TD]
[TD="align: center"]Volume3_C5[/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]Volume4[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]Volume5[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]Volume6[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Volume6_C1[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]Volume7[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
</tbody>[/TABLE]




Does this make sense ? and is this can be achieved through vba/excel function ? i would prefer vba..

Thank you!

 

[Andrew Poulsom]

In C2 copied down and across:

=IF($B2>=COLUMNS($C2:C2),$A2&"_C"&COLUMNS($C2:C2),"")

 

[AriffChowdhury]

You function worked perfectly.Thank you Sir!
But this is it what I am afraid of with the excel function actually this section is to be copied in another sheet when i ran macro to copy, it copied the formula as well (and due to reference of cell formula results an #REF! error).

 

[Andrew Poulsom]

Can't you Paste Special|Values?

 

[Rick Rothstein]

Since you said you would prefer a VBA solution (not sure why as, personally, I would use Andrew's formula solution), so here is event code that will do what you asked...

Private Sub Worksheet_Change(ByVal Target As Range)
  With Target
    If .Count > 1 Then Exit Sub
    If .Column = 2 Then
      If Not .Value Like "*[!0-9]*" Then
        Range(.Offset(, 1), Cells(.Row, Columns.Count)).Clear
        .Offset(, 1).Resize(, .Value) = Evaluate("A" & .Row & "&""_C""&COLUMN(1:" & .Value & ")")
      Else
        MsgBox "Only whole number entries are allowed!"
        .Select
      End If
    End If
  End With
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself.

 

[AriffChowdhury]

At the very first i would like to thank you to both of you Sir!...Not only i got the vba code but also got the excel function! by the dint of your supports!

And Sir Rick....Yes its worked as it is, as you described, but the area is filled with background color "pink".
When i enter any value it creates the headers with number but removed all my formatting. i wanted to paste the formatting as well into my other worksheet.


Thanks again for your cooperation!

 

[Rick Rothstein]

And Sir Rick....Yes its worked as it is, as you described, but the area is filled with background color "pink".
When i enter any value it creates the headers with number but removed all my formatting. i wanted to paste the formatting as well into my other worksheet.

Sorry, I used Clear instead of ClearContents. Try this code instead...

Private Sub Worksheet_Change(ByVal Target As Range)
  With Target
    If .Count > 1 Then Exit Sub
    If .Column = 2 Then
      If Not .Value Like "*[!0-9]*" Then
        Range(.Offset(, 1), Cells(.Row, Columns.Count)).ClearContents
        .Offset(, 1).Resize(, .Value) = Evaluate("A" & .Row & "&""_C""&COLUMN(1:" & .Value & ")")
      Else
        MsgBox "Only whole number entries are allowed!"
        .Select
      End If
    End If
  End With
End Sub

 

[AriffChowdhury]

It worked!
And when I delete any value in column B it gives me "Run Time 1004" error.


Thank you!

 

[Rick Rothstein]

And when I delete any value in column B it gives me "Run Time 1004" error.

Sorry, I did not test for that when developing the code. Try this modified version of my code instead...

Private Sub Worksheet_Change(ByVal Target As Range)
  With Target
    If .Count > 1 Then Exit Sub
    If .Column = 2 Then
      If Not .Value Like "*[!0-9]*" Then
        Range(.Offset(, 1), Cells(.Row, Columns.Count)).ClearContents
        If Len(.Value) Then .Offset(, 1).Resize(, .Value) = Evaluate("A" & .Row & "&""_C""&COLUMN(1:" & .Value & ")")
      Else
        MsgBox "Only whole number entries are allowed!"
        .Select
      End If
    End If
  End With
End Sub

 

[AriffChowdhury]

thank you so much it worked as i wanted!

thanks again