How to enter blank spaces before and after text in a particular cell

[amlan009]

This is a quickie for most ,i guess !

Basically i want to insert blank text after and before text in the column B ..........i am trying to do


For i = 1 To noofrows

Cells(i, B).Value = X
Cells(i, B).Value = vbLf & X & vbLf


Next i


I fail to understand what i am doing wrong ...please see the excel sheet attached ... i had 2 purposes of the macro ,the first i have already achieved but i am failing to insert blank spaces before and after the text ...here my code ,

Please Login or Register  to view this content.

Will appreciate ,if someone can help ,

Regards,

Amlan

[StevenM]

Just a guess here, but shouldn't it be:

X = Cells(i, B).Value

then:

Cells(i, B).Value = vbLf & X & vbLf

Also:

Dim i As Long
Dim X as Variant

[amlan009]

Thanks for the response ,that doesn't work as well ,can anyone help please?I get object
defined error at line -> X = Cells(i, B).Value

Please Login or Register  to view this content.

Apparently i guee there must be some other way of adding a space before and after the text ,i fail to understand how,i bet the solution will be so easy that i will feel like killing myself ,

Anyone has any clues ?


Regards ,

Amlan Dutta

[StevenM]

How about?

Please Login or Register  to view this content.

I overlooked that you didn't have "B" in quotes.
And there is no need to loop the same area twice.
And there is no need to use selection.
See if the above code works for you.

P.S.

You might want to change:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

Depending on what your data looks like.

[amlan009]

Steve,thanks a tonne ,i have marked the thread as solved !


i kept last row same and for data flow cannot end "If "like shown above .....But dude ,i owe you billion thanks for showing me where i was wrong ,i fill like killing myself for missing the " B".value .....i's the basics ,i sometimes make the silliest of mistakes ,

Also you are right ,i shouldn't be using 2 for loop ,that way the execution time gets doubled ,i was wondering how to do it ,after seeing your code ,i got a idea and placed it after End if rather than before -It's because i dont want the "B".value to just work for that if condition for inserting the blank spaces and want it to happen for every value in B ........


Finally million thanks to you ,the trailing code works ,

Please Login or Register  to view this content.

I attach the output file with a button called highlight which activates the Module with subroutine highlight wherein the code resides ,

Thanks a tonne ,Steve,

Humble regards ,

Amlan Dutta