I have some code I need to update. Here is the concept behind the code:
The macro starts will cell A2 and copies a customer number which finds the record in my database.
It then selects a specific position in the database to update the records to a what is equal to cell H1 in the spreadsheet. (done via copy and paste.)
What i want to do instead is to have excel copy the cell in row G that coorisponds with the customer number in column A.
A2 = 12345 *(customer Number)
G2 = $19/85 *(Price quoted to customer)
I will be updating thousands of records at the same time so this is looped to update all records based on the contents in column G of each customer number.
Please let me know if you have any suggestions. I don't want to drastically change the current code because it works well.
The portion in RED is the one that would be replaced to select the cell in row G that i would need to update.
The macro starts will cell A2 and copies a customer number which finds the record in my database.
It then selects a specific position in the database to update the records to a what is equal to cell H1 in the spreadsheet. (done via copy and paste.)
What i want to do instead is to have excel copy the cell in row G that coorisponds with the customer number in column A.
A2 = 12345 *(customer Number)
G2 = $19/85 *(Price quoted to customer)
I will be updating thousands of records at the same time so this is looped to update all records based on the contents in column G of each customer number.
Please let me know if you have any suggestions. I don't want to drastically change the current code because it works well.
The portion in RED is the one that would be replaced to select the cell in row G that i would need to update.
Rich (BB code):
Sub WPGM()
Dim i As Integer
Call ConnectWRQ
Session.TransmitANSI "WPGM"
Session.TransmitTerminalKey rcIBMEnterKey
Pause
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
Range("A" & i).Select
Selection.Copy
Session.TransmitANSI "3"
Paste
Pause
Session.TransmitTerminalKey rcIBMEnterKey
Pause
If Session.GetDisplayText(24, 2, 22) = "INVALID ACCOUNT NUMBER" Then
MsgBox "Account Number Invalid", vbCritical
Application.CutCopyMode = False
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Session.TransmitTerminalKey rcIBMClearKey
End If
Range("H1").Select
Selection.Copy
CurPos 19, 22
Pause
Paste
Session.TransmitTerminalKey rcIBMEraseEOFKey
Pause
Session.TransmitTerminalKey rcIBMEnterKey
Pause
Session.TransmitTerminalKey rcIBMPf2Key
Pause
Session.TransmitTerminalKey rcIBMPf3Key
Next i
Session.Exit
Last edited:
