VBA insert values into a range of cells

[kramart]

Hi,

I have a string of 101 values separated by comas and saved as "ppp.txt" document. I need to create a new spreadsheet called "ref1" then insert the values to each cell of the 1st column A1:A101 from this document and then name the range A1:A101 as "ppp".

Sheets.Add After:=ActiveSheet
Sheets(1).Name = "ref1"
Sheets("ref1").Activate
Range("A1:A101").Select
Range("A1:A101").Value = File.ReadAllText("C:desctop\ppp")
ActiveWorkbook.Names.Add Name:="ppp", RefersToR1C1:="=Sheet2!R1C1:R101C1"

The code doesn't work ... any help will be much appreciated !!!

Thanks,

Art

 

[kramart]

Sorry for the confusing question ... So all this so far is happening on the sheet called "ref1" which we created with your code. Now I want to in the sheet 1 insert one row above the first row and in the cells aa1, ab1 and ac1 insert names "one", "two", "three". In cells aa2, ab2 and ac3 insert three formulas that pull data from the ranges we created in sheet "ref1" (let's say SUM, COUNTIF, AVERAGE ) . I want the formulas be inserted in every cell in these columns right to the bottom of the sheet 1 so they can run on each row. The thing is that I want to run this code in 45 workbooks which have first sheets called differently like "all1", "all2" ... "all45" so the code must know which sheet is first without providing its name. Hope this make sense

 

[gallen]

Ok. One step at a time.

all this so far is happening on the sheet called "ref1" which we created with your code. Now I want to in the sheet 1 insert one row above the first row and in the cells aa1, ab1 and ac1 insert names "one", "two", "three".

Do you mean something like this? (changes in red)

Sub Import()
    Dim s As String, sLine As String
    Dim sFile As String
    Dim result() As String
    Dim ws As Worksheet
[COLOR=#ff0000][B]    Dim wsFirst As Worksheet[/B][/COLOR]
    
    'Change to your file
    sFile = "C:\Test\ppp.txt"
    
    Open sFile For Input As #1
    
    Do Until EOF(1)
        Line Input #1, sLine
        s = s & sLine
    Loop
    result = Split(s, ",")
    'Add new sheet
    Set ws = Worksheets.Add(After:=Sheets(Worksheets.Count))
    'Name sheet
    ws.Name = "ref1"
    'Paste results into new sheet
    ws.Range("A1:A101") = WorksheetFunction.Transpose(result())
[COLOR=#ff0000][B]    'set variable to first sheet in workbook[/B][/COLOR]
[COLOR=#ff0000][B]    Set wsFirst = Worksheets(1)[/B][/COLOR]
[COLOR=#ff0000][B]    'insert row[/B][/COLOR]
[COLOR=#ff0000][B]    wsFirst.Rows(1).Insert[/B][/COLOR]
[COLOR=#ff0000][B]    'Set headings on AA1,AB1 & AC1[/B][/COLOR]
[COLOR=#ff0000][B]    wsFirst.Range("AA1:AC1") = Array("One", "Two", "Three")[/B][/COLOR]
End Sub

 

[Rick Rothstein]

Your syntax is all over the place.

This code just worked for me:

Sub Import()
    Dim s As String, sLine As String
    Dim sFile As String
    Dim result() As String
    
    'Change to your file
    sFile = "C:\Test\ppp.txt"
    
    Open sFile For Input As #1
    
    Do Until EOF(1)
        Line Input #1, sLine
        [B][COLOR="#FF0000"]s = s & sLine[/COLOR][/B]
    Loop
    result = Split(s, ",")
    Range("A1:A101") = WorksheetFunction.Transpose(result())
    
End Sub

If you are anticipating the data in the file could be on multiple lines (hence, your Do..Loop), shouldn't the highlighted line of code concatenate a comma between the joined lines from the file?

s = s & "," & sLine

and then, at the end of the loop, remove the leading comma...

s = Mid(s, 2)

However, if you believe the OP when he says "I have a string of 101 values separated by commas...", then you don't actually need the Do..Loop at all.

 

[gallen]

Yes, well spotted as always Rick. If it's always on one line the loop isn't necessary. I was trying to allow for all eventualities and missed the delimiter.

 

[kramart]

Yes, exactly now I would like to enter my formulas in to AA2: end of the column AA, AB2: end of the column AB, and AC2: end of the column AC.
With regards to Ricks comment my text file contains 101 numbers separated by commas and they are on a few lines . Your code works fine for me.
Cheers Art

 

[Rick Rothstein]

With regards to Ricks comment my text file contains 101 numbers separated by commas and they are on a few lines . Your code works fine for me.

Given that, I don't see how gallen's code works fine for you without the modifications I suggested. I think you have some double values in single cells that you have not yet spotted.

 

[kramart]

Hmmm

Not sure but it works fine ... using your code the numbers are inserted into every second row ... ??

I am trying to use this code to enter my formula into every cell in the column AS starting from cell "AS3" and running until the last row with values in the coulm "E"

Range("AS3").Formula = "=IF(OR((E3="CA"),(E3="C"),(E3="A")),IF(SUMPRODUCT(--ISNUMBER(SEARCH(soc,$J19)))>0,0,1),0)"
Range("AS3", "AS" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown

??

It doesn't work at all ... what am I doing wrong ?

Cheers,

Art

 

[Rick Rothstein]

I have a string of 101 values separated by comas ...

Hmmm

Not sure but it works fine ... using your code the numbers are inserted into every second row ... ??

Then I am guessing that not only is your data within a cell separated by commas, but there is also a comma after the last item as well, correct? If so, you really should have mentioned that initially because, as you can see, it affects what the code needs to look like.

 

[kramart]

my last question ... I would really appreciate your help !

I have a txt file with words separated by commas like : work, home, key. They are saved in txt file as a column so each word is located underneath the previous one.

work,
home,
key,

How can I insert them into first row between cells A1: A3 ?


Many thanks !!!

 

[Rick Rothstein]

my last question ... I would really appreciate your help !

First off, for my own curiosity, I would like to know if my guess in Message #18 was correct or not.

I have a txt file with words separated by commas like : work, home, key. They are saved in txt file as a column so each word is located underneath the previous one.

work,
home,
key,

How can I insert them into first row between cells A1: A3 ?

Just so you know, each line in a text file created on a PC (not a Mac or Linux computer) has a Line Feed followed by a Carriage Return at the end of that line's text, so you actually have a comma followed by Line Feed followed by a Carriage Return after each of those word, not just a comma. Here is the code that will read your list into memory, split it than then distribute each file line to its own cell in Column A starting in cell A1...

[table="width: 500"]
[tr]
	[td]Sub GetCommaNewlineDelimitedTextFile()
  Dim FileNum As Long, TotalFile As String, FileLines() As String
  FileNum = FreeFile
  Open "c:\temp\temp.txt" For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  FileLines = Split(TotalFile, "," & vbNewLine)
  Range("A1").Resize(UBound(FileLines) + 1) = Application.Transpose(FileLines)
End Sub[/td]
[/tr]
[/table]