Found and paste

[Solved/Closed]
Report
-
 shehla -
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?

17 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
You want qty and userid to be pasted on NEXT row or column next to article?
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?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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\test.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
hello rizvisa
the above mentioned code is goods i just want to remove the user id information and want to replace it with code description like which code was used e.g mutation, repacking or shrinkage. it is given on top. also as a sample see below line
101051 1000MLX 12 UHT MILK O 1.000- 8 MUTATIONS AUTOM HA001
100010 everyday milk powder 2.000- 2 SHRINKAGE AUTOM HA002

i want to remove ha001 and want to replace it with SHRINKAGE

hope you understand it

please send it my hundreds of lists are pending
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
It should be on the sheet that is visible when you run the macro.
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
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.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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.
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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.
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.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
No idea what you are asking.