VBA results in wrong excel format

[Vally 88]

Hello All,

I'm using 2 VBA. the below one gives me a new sequential number looking at the previous one to create at sorted number series.
Private Sub ObtenerUltimoValorColumna()
Dim fila As Integer
Dim parar As Boolean
Dim ValorCelda As String
Dim ValorAnterior As String
fila = 32000
parar = False
Do
fila = fila - 1
' ValorAnterior = ValorCelda
ValorCelda = Sheets("Libro Banco").Range("C" & Format(fila)).Value
If ValorCelda <> "" Then parar = True
Loop While (parar = False)
txtComp.Text = ValorCelda + 1
End Sub

the second one:
Private Sub txtFecha_Change()
gives me the possibiity to select the date.

Both macros works perfectly exept for changing the format I gave to Excel. So the first one, results in a string instead of a number format and the second one change the format of the date depending from other macros applied in the same module. Now I'm not sure if this is a macro or excel issue since the macro looks alright to me and it never breaks. I have tried to change the the whole column format from xcel but after applying the macro it keeps chaging again.

They are in Spanish because I haven't build them up but just helping a friend to sort this out, so thank you for the double effort!

Cheers

 

[JLGWhiz]

ValorCelda = Sheets("Libro Banco").Range("C" & Format(fila)).Value

This will give you the format of the source cell, no matter how you have the destination cell formatted. Your options are to either format the source cell to the desired parameters or to add a code that formats the destination cell after the value has been entered. Or you can copy and paste special values and formats.

 

[Vally 88]

Hello,

Thank you for this. I have solved in this way, please find below:
Range("C" & intFila).Value = Format(txtComp, "0")

 

[Norie]

ValorCelda is declared as string so whatever value you put in it will be converted to text.

Also, if txtComp is a textbox it's going to hold text values.

 

[JLGWhiz]

Hello,

Thank you for this. I have solved in this way, please find below:
Range("C" & intFila).Value = Format(txtComp, "0")

Thanks for the feedback,
Regards, JLG