PDA

View Full Version : Solved: code to insert data to cell



copyt
03-23-2012, 01:38 AM
Hello all,

I need help for VBA code that can insert certain data to cells in use in the defined range.

For example, working range is B1:B5 but (for this time) only B2 and B3 contain certain data, let's say B2 = A, B3 = C.

So the code will insert "(x)" only to B2 and B3 (because they are not blank cells), the output will be --> B2 = A(x), B3 = C(x).

and VBA code that can reverse them back.

Any help would be appreciated.

mancubus
03-23-2012, 02:27 AM
hi.

try this



Sub InsertX()

For i = 1 To Cells(Rows.Count, "B").End(xlUp).Row
If Len(Trim(Cells(i, "B"))) Then Cells(i, "B") = Cells(i, "B") & "(X)"
Next

End Sub

copyt
03-23-2012, 02:50 AM
@ mancubus

Thank you very much for your help. How to delete "(X)" after that? :bow::bow:

Aussiebear
03-23-2012, 03:59 AM
Try the following,


Sub DeleteX()
For i = 1 To Cells(Rows.Count, “B”).End(xlUp).Row
If Len(Trim(Cells(i, ”B”) & “(X)”))Then Cells(i, “B”) & (“X”) = Cells(i, ”B”)
Next
End Sub

copyt
03-23-2012, 04:47 AM
@ Aussiebear

Thank you very much!!! :)

Meghan88
03-23-2012, 04:47 AM
Thanks so much for the help! I'm glad the solution is finally found!

mancubus
03-23-2012, 07:30 AM
Sub RemoveX()
For i = 1 To Cells(Rows.Count, "B").End(xlUp).Row
If InStr(1, Cells(i, "B").Value, "(X)") > 0 Then
Cells(i, "B") = Replace(Cells(i, "B").Value, "(X)", "")
End If
Next
End Sub

copyt
03-23-2012, 07:50 AM
@ mancubus (http://www.vbaexpress.com/forum/member.php?u=37987)

Thank you again!!!:bow::bow::bow:

mancubus
03-23-2012, 08:40 AM
you're wellcome copyt.

pls mark the thread "solved" from Thread Tools just above center of the first post in page.