VBA - Modify data in selection

I need to concatenate a string in a column of a sheet based on user selection.

Column L contains a string to which I need to add either "(SC)" or "(DR#)"
where # is a number between 1 and 7. Ideally, the users can select the rows
they wish to modify (be it 1 row or 1000), right-click the mouse and choose
the appropriate modifier.

In my code, Selekt is a public variable to hold the choice from the
right-click. Note that there are two attempts to make this work with
different coding. The first is from another forum and I think it is closer to
doing what I need. The second is what I tried at the beginning.

Here's the code I have so far:

Private Sub InsertDR()

Dim t As Long
Dim rainj as range
Dim ro as range
Dim rev As String

If Selekt = "0" Then
For i = 1 To Selection.Rows.Count
rev = CStr(Sheet1.Cells(ro, 12).Value)
rev = rev & "(SC)"
Sheet1.Cells(ro, 12).Value = rev
Next
ElseIf CInt(Selekt) < 8 Then
rainj = ActiveSheet.Selection
For Each ro In rainj
rev = CStr(Sheet1.Cells(ro, 12).Value)
rev = rev & "(DR" & Selekt & ")"
Sheet1.Cells(ro, 12).Value = rev
Next
End If
End Sub

Any help here would be hot. Thanks!
--
Adios,
Clay Harryman

NoodNutt
Thu Jul 24 21:44:36 CDT 2008

G'day Clay

I'm no MVP, just a thought, in your code example

Private Sub InsertDR()

Dim t As Long
Dim rainj as range
Dim ro as range
Dim rev As String

I can see the reference to "rainj", "ro" & "rev", but no reference to "t"

Is "t" meant to represent the reference to "Selekt"...??????

If so, then

Private Sub InsertDR()

Dim Selekt As Long
Dim rainj as range
Dim ro as range
Dim rev As String

If Selekt = "0" Then
For i = 1 To Selection.Rows.Count
rev = CStr(Sheet1.Cells(ro, 12).Value)
rev = rev & "(SC)"
Sheet1.Cells(ro, 12).Value = rev
Next
ElseIf CInt(Selekt) < 8 Then
rainj = ActiveSheet.Selection
For Each ro In rainj
rev = CStr(Sheet1.Cells(ro, 12).Value)
rev = rev & "(DR" & Selekt & ")"
Sheet1.Cells(ro, 12).Value = rev
Next
End If
End Sub

HTH
Mark.

Clayman
Fri Jul 25 06:45:02 CDT 2008

heh heh - "t" was a leftover counter that I forgot to remove. "Selekt" is a
public variable that stores the value from right-click.

Thanks for making note of that.
--
Adios,
Clay Harryman


"NoodNutt" wrote:

> G'day Clay
>
> I'm no MVP, just a thought, in your code example
>
> Private Sub InsertDR()
>
> Dim t As Long
> Dim rainj as range
> Dim ro as range
> Dim rev As String
>
> I can see the reference to "rainj", "ro" & "rev", but no reference to "t"
>
> Is "t" meant to represent the reference to "Selekt"...??????
>
> If so, then
>
> Private Sub InsertDR()
>
> Dim Selekt As Long
> Dim rainj as range
> Dim ro as range
> Dim rev As String
>
> If Selekt = "0" Then
> For i = 1 To Selection.Rows.Count
> rev = CStr(Sheet1.Cells(ro, 12).Value)
> rev = rev & "(SC)"
> Sheet1.Cells(ro, 12).Value = rev
> Next
> ElseIf CInt(Selekt) < 8 Then
> rainj = ActiveSheet.Selection
> For Each ro In rainj
> rev = CStr(Sheet1.Cells(ro, 12).Value)
> rev = rev & "(DR" & Selekt & ")"
> Sheet1.Cells(ro, 12).Value = rev
> Next
> End If
> End Sub
>
> HTH
> Mark.
>
>
>

NoodNutt
Fri Jul 25 06:56:04 CDT 2008

Glad to be of help Clay

Appreciate the feedback.

Regards
Mark.