Exchanging an Excel formula with some VB code
 

I have a formual I'm using right now that I would like to replace with some
code. Here is the formula:

=IF(D2=D1,"",IF(ISERROR(VLOOKUP($D2,'XXX
DeleteCodes'!$1:$65536,2,FALSE)),"",VLOOKUP($D2,'X XX
DeleteCodes'!$1:$65536,2,FALSE)))

I repeat that formula about 1500 times to account for most circumstances but
on occasion I need more than 1500 lines so I'd like to move this formula to
VBA code.

The deletecodes sheet contains the following data type:

X561999 DDI DDU LNI LNU OPI DDQ

If the X561999 ID is found on the current sheet then the codes to the right
of the ID is copied into the cell that contains the above formula.

Any suggestions?

Exchanging an Excel formula with some VB code
 

maybe you can use this example to help you out


+-------------------------------------------------------------------+
|Filename: search and deliver.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4598 |
+-------------------------------------------------------------------+

--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=530760

Exchanging an Excel formula with some VB code
 

Dave,

I get an invalid attachment contact your administrator message. Could you
try again to provide the download link?

"davesexcel" wrote:


maybe you can use this example to help you out


+-------------------------------------------------------------------+
|Filename: search and deliver.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4598 |
+-------------------------------------------------------------------+

--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=530760

Exchanging an Excel formula with some VB code
 

Dave,

I was able to get the zip file you wanted me to see. The file doesn't seem
to do what I'm looking for out of my program. I may not have been completely
clear about what I'm doing.

Sheet1:
User ID Role Name RACF Groups Delete Codes
Axxx001
Axxx001
Nxxx002
Wxxx108
Wxxx110
Wxxx112
Wxxx114
Wxxx114
Wxxx115

Sheet 2:
RACF ID Delete Code
A519001 PCMI
N519002 PCMI
W519108 FMI FMSI IRI LCI LNEI LNI LNMI PCMI RPI SEI TELF TII VLI VRI
W519110 FMSI IRI LCI LNEI LNI LNMI PCMI RPI SEI TELF TII VLI VRI
W519112 PCMI
W519114 CTI CTMI D2U DDI FMI FMSI LCI LNEI LNI LNMI PCMI TELF TII
W519115 DDI DOU FMI FMSI LCI LNEI LNI LNMI PCMI TELF TII

If the user ID from sheet 1 exists on sheet 2 the delete codes need to be
copied from sheet two to sheet 1. The copy can only take place once per user
ID. So for 001 and 114 the ID is listed twice but the codes should only be
copied once for the 1st ID listed. The second ID would be blank under delete
codes. I'm not sure if that made things any clearer. Hopefully the formula I
previous submitted will also help.

Thanks

"davesexcel" wrote:



maybe you can use this example to help you out


+-------------------------------------------------------------------+
|Filename: search and deliver.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4598 |
+-------------------------------------------------------------------+

--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=530760