Append name in the same cell with ' '

[Ratheesh]

please anyone can help on:
1.
Write a macro to obtain all the below names within single quotes &
separated by comma in one cell. The data can have any number of rows.
Define Dynamic range

green
blue
yellow

i.e,
in a single cell as
'green', 'blue', 'yellow'

2.
Write a macro which fills the blanks with the respective Course Name
Ids in Column A

ie.
1234 white
blue
yellow
4568 green
red


fill all the blanks column with the respective number

please help

 

[Rick Rothstein]

You didn't tell us starting cells or destination cells, so I had to guess;
change the StartCell, Destination and ColLetter to match your actual
situation. The CombineCells macro answer your first question and the
FillInTheBlanks macro answers your second question.

Sub CombineCells()
Dim X As Long, LastRow As Long, StartCell As Range, Destination As Range
Set StartCell = Range("A2")
Set Destination = Range("B2")
LastRow = Cells(Rows.Count, StartCell.Column).End(xlUp).Row
Destination.Value2 = "''" & Join(WorksheetFunction.Transpose( _
Range(StartCell, Cells(LastRow, _
StartCell.Column))), "', '") & "'"
End Sub

Sub FillInTheBlanks()
Dim Area As Range, LastRow As Long
Const ColLetter As String = "A"
On Error Resume Next
LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
For Each Area In Columns(ColLetter)(1).Resize(LastRow). _
SpecialCells(xlCellTypeBlanks).Areas
Area.Value = Area(1).Offset(-1).Value
Next
End Sub

I couldn't decide if your questions were a homework assignment or not. Just
so you know... if it is a homework assignment, then I can **absolutely
guarantee** your instructor will know you didn't write the above code.

 

[Don Guillett]

Homework?

 

[Ratheesh]

Homework?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software









- Show quoted text -

Hi -

Actually I am a bigginer in VBA, but you are right here, I was having
2 test and it should complete within 30 minutes ! I was really
worried, whether I can complete or not ?
BUT NOW I AM SO HAPPY.... I COMPLETED WITHIN 30 MINUTES WITH MY OWN
CODING !!!
If I need to paste 1 number to all the cell then it would be easy, but
here I need to paste the value for corresponding value to the below
blank cells eg:,
A B
2323 BLUE
YELLLOW
RED
4569 GREEN
ORANGE here, 2323 for Yellow and Red (A Column) & 4569 for
Orange, here it has to stop...

But as per my coding, it will continue till the end of the column,
thats why I restricted in Range("A1:A33"), I tried to take the
rows.count for B column, but I couldnt complete within the time, so I
used the range !!!
In this .FillDown I got, when I recorded for Ctrl + D !

Sub fillblanks()
For Each x In Range("A1:A33")
If x = "" Then x.FillDown
Next x
End Sub


This is for my first test, this came in my mind immediately, coz, I
was doing egs For Each loop many times last night !

Sub appendline()
For Each x In Range("A:A")
If x <> "" Then
Cells(3, 2).Value = "'" & Cells(3, 2).Value & x & "'," ' end
with a comma - again one code to remove that !
End If
Next x
End Sub



Cheeeeeeers
Ratheesh