Copy to a specific character. Excel and visual basic

Question

Copy to a specific character. Excel and visual basic

Navigation

#1 by (2 votes)

2

I am working on a program using VB and EXCEL. Currently I have to copy certain information from one cell to another to generate a report. Everything is working well for now. But I have the following doubt.

One of the lines, contains the history of notes added by the user. It was established that after each note the symbol '#' is entered to clarify that the note reaches there. The cell contains something like that.

2017-08-03 11:06 by Sojo, Andres The proposal was canceled at the request of the client on 3/8/2017. #

2017-07-18 09:47 by Sojo, Andres We continue to wait for VoBo by   part of the client. #

2017-07-14 09:52 by Sojo, Andres We continue to wait for VoBo by   part of the customer. #

2017-07-03 15:14 by Sojo, Andres The proposal is under review   by KOF. #

I am using the following code, to copy the information from the cell in the report I need.

Sheets("Extract").Range("AI" & sourceRow & "").Copy Destination:=Sheets("Print").Range("G" & destRow + 7 & "")

But this copies all the information of the cell and I only have to copy it to where the first '#' appears and stop copying the rest.

I thought about doing something like a

Do
   copiar información de la celda 
While
   no aparezca el carácter #

What happens is that with a code like that I will copy everything, since the information is in a single cell, not in several.

Thank you very much for the help.

vba excel

asked by Carlos Arronte Bello 04.09.2017 в 16:51

source

1 answer

MVC: Loading incorrect list Use startActivity within a View in Kotlin Android

score 2 · Accepted Answer

Carlos, I recommend you get the value of the cell first and cut the same from the left to the first occurrence of the character # . The code would be something like this:

texto = Sheets("Extract").Range("AI" & sourceRow & "")
texto = Left(texto, InStr(1, texto, "#") - 1)
Sheets("Print").Range("G" & destRow + 7 & "") = texto

We use instr to get the position character # and left to cut up to said position minus one (to remove the final character).

Important Note : Bear in mind that the previous code will give an error in case of not finding the character # , or we verify that it exists by if InStr(1, texto, "#") > 0 or we control the error with a On error