Posts by pyrohac

Re: VBA vs SAP

Hi all,

i have already solved this issue, maybe it will help someone:

Set SapGuiAuto = GetObject("SAPGUI")
Set SAP = SapGuiAuto.GetScriptingEngine
With SAP.Connections(0).Sessions(0)
    .createsession
    .StartTransaction ("SAP FUNCTION")
End With

hi all,

i have hopefully a simple question for you.

I am logged into sap and want to write a vba code, that will run a new function in sap under my current login (it means with no need of any password or username...).

Is it possible?

Thank you

xposted:http://www.mrexcel.com/forum/excel-q...ns-vs-sap.html and http://www.excelforum.com/excel-prog...ba-vs-sap.html

Re: VBA VLOOKUP - compare three columns

Quote from nilem;621000

Code

With workbooks("YourBook.xlsx").sheets("DK02")

In that case, if the book "YourBook.xlsx" is open now.

Thanks, your help is much appreciated.

Re: VBA VLOOKUP - compare three columns

Quote from nilem;620987

Hi Pyrohac, try it

Code

Sub ertert()
Dim x, i&, s$
With Sheets("Sheet2")
    x = .Range("A1:D" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
End With


With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For i = 1 To UBound(x)
        s = x(i, 1) & x(i, 2) & x(i, 3): .Item(s) = x(i, 4)
    Next i


    With Sheets("Sheet1")
        x = .Range("A1:C" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
    End With


    For i = 1 To UBound(x)
        s = x(i, 1) & x(i, 2) & x(i, 3)
        If .Exists(s) Then x(i, 1) = .Item(s) Else x(i, 1) = vbNullString
    Next i
End With


Sheets("Sheet1").Range("D1").Resize(i - 1).Value = x
End Sub

Display More

Hi nilem,

regarding first step "With Sheets("Sheet2")" is there any possibility to look up activesheet.name in external workbook? For example active sheet name is DK02, look up in another workbook the same name sheet and then apply what you have discribed?

Hope it is clear.

Thank you

Hi all,

i really need your help to write an macro, that will compare three columns from one sheet to another sheet, i will give you an example.
Sheet1:
A B C
100000313 NORDIC CHARGE Service Charge IS Management
100000313 NORDIC CHARGE Nordic Service Charges NO31
100000627 NORDIC CHARGE Service Charge IS Management
100000627 NORDIC CHARGE Nordic Service Charges NO31
100001227 NORDIC CHARGE Service Charge IS Management
100001227 NORDIC CHARGE Nordic Service Charges NO31
100001988 NORDIC CHARGE Service Charge IS Management
100001988 NORDIC CHARGE Nordic Service Charges NO31
100002607 NORDIC CHARGE Service Charge IS Management
100002607 NORDIC CHARGE Nordic Service Charges NO31

Sheet2:
A B C D
100000313 NORDIC CHARGE Service Charge IS Management P12 2012
100000313 NORDIC CHARGE Nordic Service Charges NO31 P11 2012
100000627 NORDIC CHARGE Service Charge IS Management P11 2012
100000627 NORDIC CHARGE Nordic Service Charges NO31 P12 2012
100001227 NORDIC CHARGE Service Charge IS Management P12 2012
100001227 NORDIC CHARGE Nordic Service Charges NO31 P09 2012
100001988 NORDIC CHARGE Service Charge IS Management P08 2012
100001988 NORDIC CHARGE Nordic Service Charges NO31 P05 2012
100002607 NORDIC CHARGE Service Charge IS Management P04 2012
100002607 NORDIC CHARGE Nordic Service Charges NO31 P03 2012

I need macro, that will fill Column D from "Sheet2" to column D in "Sheet1" if values in columns A,B,C (together) in "Sheet1" will be found in columns A,B,C in "Sheet2". Is there any possibility to do this without using function "&" ????

THank you, your help is much appreciated.