Found and paste
Solved/Closed
Related:
- Found and paste
- How to paste photo in resume - Guide
- Copy and paste fonts - Guide
- Insert a new sheet at the end of the tab names and paste the range names starting in cell a1. autofit columns a:b and name the worksheet as range names. ✓ - Excel Forum
- Pi symbol copy and paste - Guide
- Compare two worksheets and paste differences to another sheet - excel vba free download - Excel Forum
17 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Mar 24, 2010 at 06:48 AM
Mar 24, 2010 at 06:48 AM
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?
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
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Mar 26, 2010 at 11:52 AM
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
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
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
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
Didn't find the answer you are looking for?
Ask a question
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 4, 2010 at 11:35 AM
Apr 4, 2010 at 11:35 AM
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
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 5, 2010 at 11:44 AM
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
To
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.
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
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 5, 2010 at 11:52 PM
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.
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
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
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 6, 2010 at 02:52 PM
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.
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.
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
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 7, 2010 at 05:50 AM
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
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
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
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 15, 2010 at 08:11 AM
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
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
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
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 4, 2010 at 09:57 AM
May 4, 2010 at 09:57 AM
No idea what you are asking.