Previous Topic Next Topic

Found and paste

Solved/Closed
shehla - Mar 24, 2010 at 01:55 AM
 shehla - Aug 4, 2010 at 01:44 PM
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 responses

Answer 1 / 17
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 24, 2010 at 06:48 AM
You want qty and userid to be pasted on NEXT row or column next to article?
0
Answer 2 / 17
shehla
Mar 26, 2010 at 08:28 AM
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?
0
Answer 3 / 17
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 26, 2010 at 11:52 AM
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
0
shehla
Aug 4, 2010 at 01:44 PM
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
0
Answer 4 / 17
shehla
Apr 3, 2010 at 10:49 PM
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
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 17
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 4, 2010 at 11:35 AM
It should be on the sheet that is visible when you run the macro.
0
Answer 6 / 17
shehla
Apr 5, 2010 at 11:30 AM
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
0
Answer 7 / 17
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 5, 2010 at 11:44 AM
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
0
Answer 8 / 17
shehla
Apr 5, 2010 at 11:23 PM
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.
0
Answer 9 / 17
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 5, 2010 at 11:52 PM
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.
0
Answer 10 / 17
shehla
Apr 6, 2010 at 02:47 PM
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
0
Answer 11 / 17
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 6, 2010 at 02:52 PM
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.
0
Answer 12 / 17
shehla
Apr 7, 2010 at 12:43 AM
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.
0
Answer 13 / 17
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 7, 2010 at 05:50 AM
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
0
Answer 14 / 17
shehla
Apr 15, 2010 at 03:14 AM
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
0
Answer 15 / 17
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 15, 2010 at 08:11 AM
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
0
Answer 16 / 17
shehla
May 4, 2010 at 08:11 AM
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
0
Answer 17 / 17
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 4, 2010 at 09:57 AM
No idea what you are asking.
0

Similar discussions

Can no longer paste text into insta
6hypnone -
Richierichmeister -

6 responses
Deleted Message icon
thina -
BunoCS -

1 response
Comparing Two Excel Sheets and copying like data to third sheet
coz2992 -
TrowaD -

11 responses
VBA Compare 2 sheets and output difference to 3rd sheet
jmah -
TrowaD -

3 responses
Macro to compare 2 sheets and copy differences
ehusar -
AirMaple -

2 responses