A quick question:

[Mr.Karr]

Hello

Can anyone please modify the below snippet for below;

-presently for an ex. TextBox7 value will be pasted in row#8 but I need that value to be pasted all the visible cells under row#8.

Can this be done with the existing code or loop needed ? pls help

erow = Sheets("Main Database").Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 8) = TextBox7.Text
Cells(erow, 14) = txtPhone.Text
Cells(erow, 32) = ComboBox6.Text
Cells(erow, 37) = "Modified"

 

[Hui]

Define below row 8 ?
as there are over 1,000,000 rows below row 8

 

[David Evans]

Define below row 8 ?
as there are over 1,000,000 rows below row 8

2^20 - 8 to be exact, mate

Trust you're well down under

 

[Hui]

erow = Sheets("Main Database").Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row
LastRow=20
Range(Cells(erow, 8),Cells(LastRow, 8)) = TextBox7.Text
Range(Cells(erow, 14),Cells(LastRow, 14)) = txtPhone.Text
Range(Cells(erow, 32),Cells(LastRow, 32)) = ComboBox6.Text
Range(Cells(erow, 37),Cells(LastRow, 37)) = "Modified"

 

[Mr.Karr]

thanks @Hui : you've been very helpful

 

[Mr.Karr]

@Hui : need your help on this.
This was actually working well. Filling up all the filtered/visible cells with the text from userform. But today I think I have cleared some content from other buttons & probably some "Dim" declaration would've got away with it.

Can you please check below & advise. Thanks

erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Dim lastrow As Long
lastrow = 21
Range(Cells(erow, 10), Cells(lastrow, 10)) = TextBox7.Text
Range(Cells(erow, 9), Cells(lastrow, 9)) = ComboBox5.Text
Range(Cells(erow, 11), Cells(lastrow, 11)) = TextBox4.Text
Range(Cells(erow, 8), Cells(lastrow, 8)) = txtLname.Text
Range(Cells(erow, 13), Cells(lastrow, 13)) = ComboBox4.Text
Range(Cells(erow, 14), Cells(lastrow, 14)) = txtPhone.Text
Range(Cells(erow, 12), Cells(lastrow, 12)) = TextBox5.Text
Range(Cells(erow, 15), Cells(lastrow, 15)) = TextBox6.Text
Range(Cells(erow, 17), Cells(lastrow, 17)) = ComboBox2.Text
Range(Cells(erow, 32), Cells(lastrow, 32)) = ComboBox6.Text
Range(Cells(erow, 37), Cells(lastrow, 37)) = "Modified"
Range(Cells(erow, 38), Cells(lastrow, 38)) = Environ("USERNAME")
Range(Cells(erow, 39), Cells(lastrow, 39)) = Now
Cells(erow, 1) = ActiveSheet.Range("A19").Text
Cells(erow, 2) = ActiveSheet.Range("B19").Text

Is there something incorrect with the above code? Should I need to declare anything in addition to it ?

 

[Mr.Karr]

Also, as soon as I click on "Submit", the error msg I'm getting is below;
Run-time error '1004'
Application-defined or object-defined error

and highlighting below line;

Range(Cells(erow, 10), Cells(lastrow, 10)) = TextBox7.Text

Please advise

 

[Hui]

This works ok for me
This suggests it is a data / definition style error

Can you please attach your file

 

[Hui]

Have you tried changing the next line to

Range(Cells(erow, 9), Cells(lastrow, 9)) = ComboBox5.value

 

[Mr.Karr]

@Hui : this only works with nonfiltered range. But the requirement which I have is to modify/take the value of combobox/textbox

sadly this stopped to work with the file. However this was working well before. Please advise if I removed anything by mistake.

 

[Mr.Karr]

yes, I did try changing .text to .value. No improvement. Still getting err

 

[Mr.Karr]

@Luke M : recent update: I checked this with another workbook, this works. But not with the actual file in which I've built a tool.

I'm confused why. Do you think I have somewhere set thisworksheet as activesheet and forgot to release to normal ? Pls advise

 

[Hui]

Can you post the file?

 

[Mr.Karr]

For some reasons unable to post file here, confi.stuffs. Found an alternate way to get this done. thanks Hui.