found and paste Solved/Closed

Question

Hello,
i have two files, one is excel which contains few articles like 111230, 111412, 552136 etc and other file is notepad which contains hundreds of articles. what i want to do is a macro code which find the excel few articles in notepad and paste into excel in very next row of each article. example is given below.
excel sheet
article#   qty    userid
111230
111412
552136
now notepad file
article#   qty    userid 
111231   20     al002
111230   01     ha001
115216   0.5   al002
552136  50     ha002
now if article# 111230 is in notepad then the qty and userid from notepad paste automatically into excel in the next row of that particular article.

can anybody help me?

rizvisa1 · Answer

You want qty and userid  to be pasted on NEXT row or column next to article?

shehla · Answer

yes i want qty and userid pasted into excel in the very next row of mentioned articles in excel from notepad file.
a macro or vba code is require.

i have two files, one is excel which contains few articles like 111230, 111412, 552136 etc and other file is notepad which contains hundreds of articles. what i want to do is a macro code which find the excel few articles in notepad and paste into excel in very next row of each article. example is given below. 
excel sheet 
article# qty userid 
111230 
111412 
552136 
now notepad file 
article# qty userid 
111231 20 al002 
111230 01 ha001 
115216 0.5 al002 
552136 50 ha002 
now if article# 111230 is in notepad then the qty and userid from notepad paste automatically into excel in the next row of that particular article. 

can anybody help me?

rizvisa1 · Answer

Assumptions
1. The text file has the header on first row
2. the text file is space delimited

Sub moveRows()
Dim iFileNum As Integer
Dim sTemp As String
Dim sArt As String
Dim sQty As String
Dim sUid As String
Dim iLoc As Integer
Dim lLineNum As Long
Dim lMatchRow As Long

    iFileNum = FreeFile
    lLineNum = 0
    
    Open "c:\hypsnap32	est.txt" For Input As iFileNum
    
    Do While Not EOF(iFileNum)
        sTemp = ""
        sArt = ""
        sQty = ""
        sUid = ""
        iLoc = 0
        lMatchRow = -1
        
        Line Input #iFileNum, sTemp
        
        sTemp = Trim(sTemp)
        If (sTemp <> "") Then lLineNum = lLineNum + 1
        
        If (lLineNum > 1) Then
        
            iLoc = InStr(1, sTemp, " ")
            If (iLoc > 0) Then
                sArt = Trim(Left(sTemp, iLoc - 1))
                sTemp = Trim(Mid(sTemp, iLoc + 1))
            Else
                sArt = sTemp
                sTemp = ""
            End If
            
            iLoc = InStr(1, sTemp, " ")
            If (iLoc > 0) Then
                sQty = Trim(Left(sTemp, iLoc - 1))
                sUid = Trim(Mid(sTemp, iLoc + 1))
            Else
                sQty = sTemp
                sTemp = ""
            End If
            
            On Error Resume Next
            lMatchRow = WorksheetFunction.Match(CLng(sArt), Range("A:A"), 0)
            On Error GoTo 0
        
            If (lMatchRow > 0) Then
                Rows(lMatchRow + 1).Insert xlDown
                   
                Cells(lMatchRow + 1, "B") = sQty
                Cells(lMatchRow + 1, "C") = sUid
            End If
        End If
        
    Loop
    
    Close (iFileNum)
    
End Sub

shehla · Answer

your answer was as mentioned below. i have pasted it but its not showing the result in the excel sheet i have opened. can you please tell me where it is loading the data and in which excel sheet?

Sub moveRows()
Dim iFileNum As Integer
Dim sTemp As String
Dim sArt As String
Dim sQty As String
Dim sUid As String
Dim iLoc As Integer
Dim lLineNum As Long
Dim lMatchRow As Long

    iFileNum = FreeFile
    lLineNum = 0
    
    Open "C:\Documents and Settings\Jun@id\Desktop\STSTOL102010089.lst" For Input As iFileNum
    
    Do While Not EOF(iFileNum)
        sTemp = ""
        sArt = ""
        sQty = ""
        sUid = ""
        iLoc = 0
        lMatchRow = -1
        
        Line Input #iFileNum, sTemp
        
        sTemp = Trim(sTemp)
        If (sTemp <> "") Then lLineNum = lLineNum + 1
        
        If (lLineNum > 1) Then
        
            iLoc = InStr(1, sTemp, " ")
            If (iLoc > 0) Then
                sArt = Trim(Left(sTemp, iLoc - 1))
                sTemp = Trim(Mid(sTemp, iLoc + 1))
            Else
                sArt = sTemp
                sTemp = ""
            End If
            
            iLoc = InStr(1, sTemp, " ")
            If (iLoc > 0) Then
                sQty = Trim(Left(sTemp, iLoc - 1))
                sUid = Trim(Mid(sTemp, iLoc + 1))
            Else
                sQty = sTemp
                sTemp = ""
            End If
            
            On Error Resume Next
            lMatchRow = WorksheetFunction.Match(CLng(sArt), Range("A:A"), 0)
            On Error GoTo 0
        
            If (lMatchRow > 0) Then
                Rows(lMatchRow + 1).Insert xlDown
                   
                Cells(lMatchRow + 1, "B") = sQty
                Cells(lMatchRow + 1, "C") = sUid
            End If
        End If
        
    Loop
    
    Close (iFileNum)
    
End Sub

rizvisa1 · Answer

It should be on the sheet that is visible when you run the macro.

shehla · Answer

you wrote 1linenum but all subjects are placed in linenum5. can you help me? moreover there are lots of user ids i just want to load ha001, ha002 and al002 users in that sheet. your usual cooperation will be highly appreciated. thanks

rizvisa1 · Answer

Not sure what you meant when you said "you wrote 1linenum but all subjects are placed in linenum5. "

For part two, you can modify this portion of script

            On Error Resume Next
            lMatchRow = WorksheetFunction.Match(CLng(sArt), Range("A:A"), 0)
            On Error GoTo 0
        
            If (lMatchRow > 0) Then
                Rows(lMatchRow + 1).Insert xlDown
                   
                Cells(lMatchRow + 1, "B") = sQty
                Cells(lMatchRow + 1, "C") = sUid
            End If


To


          if ((sUid = "ha001") or (sUid = "ha002") or (sUid  = "al002")) then 

                On Error Resume Next
                lMatchRow = WorksheetFunction.Match(CLng(sArt), Range("A:A"), 0)
                On Error GoTo 0
        
                If (lMatchRow > 0) Then
                    Rows(lMatchRow + 1).Insert xlDown
                   
                    Cells(lMatchRow + 1, "B") = sQty
                    Cells(lMatchRow + 1, "C") = sUid
                End If

           end if

shehla · Answer

hello,
below mentioned is the shape of notepad file.
STSLOL      TELLING UNIVERSITY       05-05-2010   LINE1
= = = = = = = = = = = = = = = = = = = = == = = = = = =  LINE 2
                                                                                             LINE 3
SUB TRY DO                                                                    LINE 4
ARTICLE  DESCRIPTION  PK TI CORR-QTY VALUE  USERID LINE5
111418   COKE                    4    1   -1                  500       HA001
111425   SPRITE                 5    1    8                   450      MUTATION

the code that you have provided when i load it, it load all data. i just need article number, description, corr-qty and userid. in the last message i wrote that line5 contains subjects and further whole articles are placed below subject line. line1,line2,line3 and line4 are just introduction of sheet and nothing. main material start from line5. hope you understand.

looking forward for your kind response.

rizvisa1 · Answer

For line number you can change it here  

If (lLineNum > 1) Then   

This was saying process from line number 2 and onward  

You can change it to

If (lLineNum > 5)   

For your other issue, the code was based on the sample data which only had three column that was space delimited. You need to explain how the text file is constructed. By that I mean how one can say when one column ends and other starts. Is it a fixed length file or has a special delimited character. you need to provide answers to these questions.

shehla · Answer

below is pasted data that is printed on daily basis as mentioned below. its fixed length file. on daily basis it is printed on this format.
L10.201  S T O C K  C O R R E C T I O N S   STORE 10  DATE: 30-03-2010    PAGE:  34

======= ===================== ==== == = = ===== === === =========== ================  
ART DESCR    SU PT T I   NO GRP GRP   CORR_QTY  CORR_VALUE    SELL_PRICE CD  USERID  
        STOCK_CORR
======= ===================== ==== == = = ===== === === =========== ================ 
 137517 HAVOLINE 3LTRS   1 PI 1 3 20891 350 1  4.000    3,076.00  769.00  8 MUTATIONS   AUTOM

rizvisa1 · Answer

Shehla, I am sorry but I do not recognize the columns, and hence it is not possible for me to tell how columns are laid out here. Could you tell about each columns both start point and end point.

shehla · Answer

rizvisa1. if there is any option for attached file in this forum it would be easy for you to understand. anyhow i try my best to explain. there are 15 coulmns (writing which coulmn is desired to be loaded into sheet and which is undesired) namely
article no   (desired)
article description   (undesired)
Su               (undesired)
pt                 (undesired)
ST               (undesired)
DI                (undesired)
supplier no  (undesired)
article group no   (undesired)
article sub group no    (undesired)
correction qty    (desired)
correction value  (undesired)
correction selling price   (undesired)
code           (undesired)
description  (undesired)
user id      (desired)

and each coulmn contains its data in sequence.
  
hope above mentioned detailed helped you a lot.

rizvisa1 · Answer

You can upload the file at some shared site like https://authentification.site and paste link. You did not answer my question, I am asking about location of the columns like start at position 10 and end at position 25, next one starts at 39 and ends at 42 etc Since you say its is a fixed length file, each column must have a starting character location and ending position

shehla · Answer

rizvisa please find the below link to upload file. it will help you a lot to make a code as may be there was a possiblility of communication error from my side that couldn't explain of what i need.

https://authentification.site/files/21950426/data.lst

and below is the excel sheet to put userid, corr_qty in the very next row of that article.

https://authentification.site/files/21950485/Book1.xls

rizvisa1 · Answer

this is based on your files


Sub moveRows()
Dim iFileNum As Integer
Dim sTemp As String

Dim sArt As String 'STORES ART_NO
Dim sDes As String ' STORES DESCR
Dim sQty As String 'STORES CORR_QTY
Dim sUid As String 'STORES USERID

Dim lMatchRow As Long ' WHAT ROW HAS MATCH FOR ART_NO


    iFileNum = FreeFile
    lLineNum = 0
    
    Open "C:\Users\haadi\Downloads\data.lst" For Input As iFileNum
    
    Do While Not EOF(iFileNum)
        sTemp = ""
        sArt = ""
        sDes = ""
        sQty = ""
        sUid = ""

        lMatchRow = -1
        
        Line Input #iFileNum, sTemp
        
        sTemp = Trim(sTemp)
         
        sArt = Trim(Mid(sTemp, 1, 7)) ' GET FROM CHARACTER 1, NEXT 7 CHARACTERS
        sDes = Trim(Mid(sTemp, 8, 21)) ' GET FROM CHARACTER 8, NEXT 21 CHARACTERS
        sQty = Trim(Mid(sTemp, 56, 11)) ' GET FROM CHARACTER 56, NEXT 11 CHARACTERS
        sUid = Trim(Mid(sTemp, 114, 6)) ' GET FROM CHARACTER 114, NEXT 6 CHARACTERS
        
        'ONLY PROCESS IF USERID IS ONE OF THE BELOW
        If ((UCase(sUid) = "HA001") Or _
            (UCase(sUid) = "HA002") Or _
            (UCase(sUid) = "AL002")) Then

            On Error Resume Next
            
            'FIND WHERE THE ARTICE NUMBER IS ON SHEET
            lMatchRow = WorksheetFunction.Match(CLng(sArt), Range("A:A"), 0)
            
            On Error GoTo 0
    
            ' IF USERID IS FOUND ON THE SHEET
            If (lMatchRow > 0) Then
                Rows(lMatchRow + 1).Insert xlDown
               
                Cells(lMatchRow + 1, "C") = sQty
                Cells(lMatchRow + 1, "D") = sUid
            End If

        End If
    
    Loop
    
    Close (iFileNum)

End Sub

shehla · Answer

hello rizvisa,
your code working is properly. i want one more thing in the sheet is only update sharinkage code against user ha001,ha002, al002 of the articles in the very next row.  for your ready reference i am sending you the link of excel and notepad, also the code is in excel.
https://authentification.site/files/22264826/code.xls
https://authentification.site/files/22264844/data.lst 
looking forward for your kind response

rizvisa1 · Answer

No idea what you are asking.

Found and paste

17 responses

Similar discussions

Subscribe To Our Newsletter!