If not last cell in selection range.

Wenggo · December 6th, 2013, 07:29 PM

Can anybody correct my excel macro code as below, failed to execute.

Sub add_comma()
Dim r As Range

With Selection

For Each r In Selection
If r.Value not_last_cell Then
r.Value = " '" & r.Value & "'" & ","
Else
r.Value = " '" & r.Value & "'"
Endif
Next

End With
Excel.Columns.AutoFit
Excel.Rows.AutoFit
End Sub

Shasur · December 6th, 2013, 08:33 PM

Hi

Can you brief what this macro is intended to do..it would be helpful

Regards
Shasur

Wenggo · December 6th, 2013, 10:14 PM

Quote:

Originally Posted by Shasur

Hi

Can you brief what this macro is intended to do..it would be helpful

Regards
Shasur

Thanks for reply dear friend, I want to put comma for each cells that contains value in range selected.

For last cell which contains value, i dont want to put a comma.

What should I code, valiue is not_last_cell in macro ?

With Selection

For Each r In Selection
If r.Value not_last_cell Then
r.Value = " '" & r.Value & "'" & ","
Else
r.Value = " '" & r.Value & "'"
Endif
Next

End With

Example output.

'No',
'Comma',
'at',
'Last',
'Cell'

---see no comma for value at last cell---

Thanks buddy..

Shasur · December 7th, 2013, 08:57 PM

Hi

You need not check the last cell in each iteration
This will increase the time of execution. Instead you can use the offset function to 'adjust' your range

Set oRng = Selection.Range
Set oRng = oRng.Offset (-1,0)
For each can start from here

Cheers
Shasur

Wenggo · December 8th, 2013, 07:47 AM

Quote:

Originally Posted by Shasur

Hi

You need not check the last cell in each iteration
This will increase the time of execution. Instead you can use the offset function to 'adjust' your range

Set oRng = Selection.Range
Set oRng = oRng.Offset (-1,0)
For each can start from here

Cheers
Shasur

I am sorry friend, I am new in excel macro, I couldnt get the output as needed when try executed. Can you correct my code below in order to get : -

Example output.

'No',
'Comma',
'at',
'Last',
'Cell'

---see no comma for value at last cell---

Sub add_comma()
Dim oRng As Range
With Selection
Set oRng = Selection.Range
Set oRng = oRng.Offset(-1, 0)
For Each oRng In Selection
If oRng.Value <> "" Then
oRng.Value = " '" & oRng.Value & "'" & ","
Else
oRng.Value = " '" & oRng.Value & "'"
End If
Next
End With
Excel.Columns.AutoFit
Excel.Rows.AutoFit
End Sub

Wenggo · December 10th, 2013, 09:31 PM

Quote:

Originally Posted by Wenggo

I am sorry friend, I am new in excel macro, I couldnt get the output as needed when try executed. Can you correct my code below in order to get : -

Example output.

'No',
'Comma',
'at',
'Last',
'Cell'

---see no comma for value at last cell---

Sub add_comma()
Dim oRng As Range
With Selection
Set oRng = Selection.Range
Set oRng = oRng.Offset(-1, 0)
For Each oRng In Selection
If oRng.Value <> "" Then
oRng.Value = " '" & oRng.Value & "'" & ","
Else
oRng.Value = " '" & oRng.Value & "'"
End If
Next
End With
Excel.Columns.AutoFit
Excel.Rows.AutoFit
End Sub

Anybody world ?

Shasur · December 11th, 2013, 01:12 PM

Hi

Can you try with this hint

Code:

Sub add_comma()

Dim oRng As Range
Set oRng = ActiveWorkbook.Sheets(3).Range("G1:G4")


For Each oCell In oRng.Cells
    If oCell.Value <> "" Then
        oCell.Value = " '" & oCell.Value & "'" & ","
    Else
        oCell.Value = " '" & oCell.Value & "'"
    End If
Next oCell
End Sub

Cheers
Shasur