Userform - Filling down info on rows

[heytoluca]

Hello,

Could anyone please help me with my code? I don't know what I'm doing wrong, it works perfectly on one file (Remision), but for some reason it won't work on the other one (CI Fill).

It is a simple userform that helps creating some documents. I attach them for your reference.

What the code does:
It's a userform that fills some basic information on the document, then it uses a second part of the userform to fill down information on a row by row basis. You fill the info, click the button and it fills row 1, click it again and it fills row 2, etc.

This is the code that won't work on the CI Fill file:

Private Sub Cmd2_Click()


Dim LastRow As Long, ws As Worksheet


    Set ws = Sheets("Factura")
    
    
    LastRow = ws.Range("A46").End(xlUp).Row + 1 'Finds the last blank row
    ws.Range("A" & LastRow).Value = PosTxt.Text 'Línea
    ws.Range("A" & LastRow).Offset(0, 1).Value = TextBox7.Value 'Cantidad
    ws.Range("A" & LastRow).Offset(0, 2).Value = TextBox9.Value 'Descripción
    ws.Range("A" & LastRow).Offset(0, 3).Value = TxtSerial.Value 'Seriales
    ws.Range("A" & LastRow).Offset(0, 4).Value = TextBox8.Value 'Precio Unitario
    
    PosTxt.Value = PosTxt + 1
    TextBox7.SetFocus

Here are the files:
https://www.dropbox.com/s/rqoxyeh17wvwwi8/Remision.xlsm?dl=0
https://www.dropbox.com/s/djz2xabnla5rue0/CI Fill.xlsm?dl=0



Regards,
David

 

[Roderick_E]

when you say it doesn't work, do you get an error message? I like to test the code by placing msgbox "Test" in different lines so see if it is executing that line or not.

 

[heytoluca]

when you say it doesn't work, do you get an error message? I like to test the code by placing msgbox "Test" in different lines so see if it is executing that line or not.

Hi Roderick,

Wow that was a quick, thanks for replying.

Here's what the code should do: https://gyazo.com/ce7dbe0108eaa20805b035683f653f99
It's the same code, I just changed control names and ranges.

And here's the same code not working: https://gyazo.com/26201739254c60c5fa45e9fc1678bd17

 

[heytoluca]

when you say it doesn't work, do you get an error message? I like to test the code by placing msgbox "Test" in different lines so see if it is executing that line or not.

Hi Roderick,

Tested as you suggested.

All lines are working. Still my code doesn't work on that specific file

Please see: https://gyazo.com/c1b91523c50ade7a5028d9bf1df8835b



Regards,
David

 

[Roderick_E]

I suspect it has something to do with the lastRow code. I suspect you actually have a row populated waaay down on the sheet. So it really IS adding the new stuff, you just aren't seeing it. Investigate that possibility. To test you could add code:
MSGBOX LastRow then remove it after you are done.

 

[Roderick_E]

Hmmm no, that would be it. But I did download your two workbooks and on the malfunctioning one, as soon as I downloaded it, it thew and error msg that it "couldn't load object because it was not available on this machine" -- this is probably the issue.

 

[Roderick_E]

aha -- the MERGED rows are causing the issue. It never advances to the next blank row since row 27 and 28 are merged, it attempts to input the next line on row 28. Unmerge rows or insert code that skips one line.

 

[heytoluca]

aha -- the MERGED rows are causing the issue. It never advances to the next blank row since row 27 and 28 are merged, it attempts to input the next line on row 28. Unmerge rows or insert code that skips one line.

Aha! That partially solved the issue.

Please see attached file: https://www.dropbox.com/s/41w8bszrhpmshzj/CI Fill.xlsm?dl=0

Now the code moves down, but it only fills 3 columns, I'm still missing the rest. They are not merged anymore.



Regards,
David

 

[Roderick_E]

Aha! That partially solved the issue.

Please see attached file: https://www.dropbox.com/s/41w8bszrhpmshzj/CI Fill.xlsm?dl=0

Now the code moves down, but it only fills 3 columns, I'm still missing the rest. They are not merged anymore.


Regards,
David

There were also some merged columns causing the same issue. When you have just a few rows/columns to work with there is really no reason to use OFFSET (unless you anticipate inserting columns later) Just hard code the columns in like I did below:

Private Sub Cmd2_Click()


Dim LastRow As Long, ws As Worksheet


    Set ws = Sheets("Factura")
    
      
    LastRow = ws.Range("A39").End(xlUp).Row + 1 'Finds the last blank row
    ws.Range("A" & LastRow).Value = PosTxt.Text 'Línea
    ws.Range("B" & LastRow).Value = TextBox7.Value 'Cantidad
    ws.Range("C" & LastRow).Value = TextBox9.Value 'Descripción
    ws.Range("G" & LastRow).Value = TxtSerial.Value 'Seriales
    ws.Range("H" & LastRow).Value = TextBox8.Value 'Precio Unitario
    
    PosTxt.Value = PosTxt + 1
    TextBox7.SetFocus    






End Sub

 

[heytoluca]

There were also some merged columns causing the same issue. When you have just a few rows/columns to work with there is really no reason to use OFFSET (unless you anticipate inserting columns later) Just hard code the columns in like I did below:

Private Sub Cmd2_Click()


Dim LastRow As Long, ws As Worksheet


    Set ws = Sheets("Factura")
    
      
    LastRow = ws.Range("A39").End(xlUp).Row + 1 'Finds the last blank row
    ws.Range("A" & LastRow).Value = PosTxt.Text 'Línea
    ws.Range("B" & LastRow).Value = TextBox7.Value 'Cantidad
    ws.Range("C" & LastRow).Value = TextBox9.Value 'Descripción
    ws.Range("G" & LastRow).Value = TxtSerial.Value 'Seriales
    ws.Range("H" & LastRow).Value = TextBox8.Value 'Precio Unitario
    
    PosTxt.Value = PosTxt + 1
    TextBox7.SetFocus    






End Sub

How great can you be?

That was awesome! Learned something new today.

Thank you very much.



Regards,
David