PDA

View Full Version : [SOLVED] Fill Column



joeny0706
11-19-2019, 12:23 PM
Hi all





If anyone has a couple minutes to help, I would be very appreciative.I currently receive data that is sales orders. I am importing this data into myERP program. I just need something simple to most of you I am guessing. I amnew to VBA
I have a excel sheet that I need to turn into a csv file. Ifirst need to fill a column only as far as how much data is in the row next toit. If that makes sense.





So I need to have column C filled with “Company A”. I willalso use the same to fill other columns. I will be able to figure out how tochange what column and what data it fills it with. I just wondering if someonecould help me with the main code.



In the attached sheet you will see the sales orders. I needcolumn C to have “company A” in all the cells until the end. In this case thatwould be line 36. They will all be different lengths.



Thanks to any one with extra time to help me.

Logit
11-19-2019, 01:01 PM
.
There are many ways to accomplish this. Here is one ... a macro :

Paste in a regular module :



Option Explicit




Sub test()


Dim lastRow As Long
lastRow = Range("D" & Rows.Count).End(xlUp).Row
Range("C2").AutoFill Destination:=Range("C2:C" & lastRow)


End Sub

Enter “Company A” in C2 then run the macro.

joeny0706
11-19-2019, 01:29 PM
Thanks

Works Perfect

joeny0706
11-19-2019, 01:37 PM
How can I get it to fill the first 3 columns

So If I was to put
Company a in A2 and Company b in B2 and Company c in C2 itwill auto fill all 3 columns

joeny0706
11-19-2019, 01:46 PM
Sub FillColumnEnterColumnABC()


Dim lastRow As Long
lastRow = Range("D" & Rows.Count).End(xlUp).Row
Range("C2").AutoFill Destination:=Range("C2:C" & lastRow)

lastRow = Range("C" & Rows.Count).End(xlUp).Row
Range("B2").AutoFill Destination:=Range("B2:B" & lastRow)

lastRow = Range("B" & Rows.Count).End(xlUp).Row
Range("A2").AutoFill Destination:=Range("A2:A" & lastRow)

End Sub





I did get it to work. But if I have test1 in A2 it fills itin a series, Test1 Test2 Test3 and so on. What do I need to do so it onlycopies and wont autofill in a series?

Logit
11-19-2019, 05:19 PM
Option Explicit


'Enter term in C2 and run macro
Sub test()


Dim lastRow As Long
lastRow = Range("D" & Rows.Count).End(xlUp).Row
Range("C2").AutoFill Destination:=Range("C2:C" & lastRow)
Range("B2").AutoFill Destination:=Range("B2:B" & lastRow)
Range("A2").AutoFill Destination:=Range("A2:A" & lastRow)






End Sub

snb
11-20-2019, 02:55 AM
Sub M_snb()
Cells(1).CurrentRegion.Offset(1).Resize(, 3) = Array("Company 123", "Company 345", "Company996")
End Sub

joeny0706
11-20-2019, 07:16 AM
Snb




Yours is nice because there will be times it will need to bea static value. In that case I would not have to type anything in line two using yours.

When I run yours all it does is fill line two in columna,b,c with company***, but it does not go all the way to the end “using columnD”
If I run it two times it does fill all the way down but alsoone line two far.



Some times I will need to chose what I need in the columns “in that case Logit will work perfect” but others I will always need the same and in that case yours would be perfect.

I need to try and make this as simple as possible. It will be fine if I am the one converting and uploading the sales orders. But if I am not here for some reason I will need someone else to do it and want to have as little user interaction as possible. Others are hard to teach at times.


Snb do you know why it is only entering data into line two and not all of them? Is it something more I need to do?

Thanks

snb
11-20-2019, 08:43 AM
If your file does not match the sample file you posted.....

The code runs perfectly well in your sample file.
You should always post a representative file.
Can you please magnify the font you are writing with in this forum ?


Sub M_snb()
with sheet1.cells(1)
.resize(,3)=Array("RtN0","CustNo","CustDesc")
.CurrentRegion.Offset(1).Resize(, 3) = Array("Company 123", "Company 345", "Company996")
End With
End Sub

joeny0706
11-20-2019, 09:29 AM
I apologize I did not have a header in row one.There will be a header so that is fine.
When Ido run it on the sample fill I attached in post one it did go one row to far. Iattached the results from running it on the sample file I provided.

I amnot sure what you mean by magnify when I write in this forum. Do you mean fontsize?
I dowritein Microsoft word first then paste it into the reply box. When I type inthis forum it skips letters I type. It seems like autosave causing the issue. Itried to find how to shut autosave but was notable to find a way
I think autosave does ause issues when I type. As you can see in this lastsentacethere are lettersand space missing. Is there a way to shu autosave. Evenwhen I paste text into this reply box it removes letters and spaces.


Thanks and I do apreciate your help

I keep tring to save with font size bigger but it keeps going back when I save it

snb
11-20-2019, 03:18 PM
When Ido run it on the sample fill I attached in post one it did go one row too far.

And you thought I didn't realize that ? :bug:
I'd call it 'futile'.

Why don't you analyse the code and try to remove that last 'row' yourself.
The code you have received provides for 99.997 % of your request. 0.003 % up to you.

joeny0706
11-20-2019, 10:44 PM
Thanks all