I gang

Writing Excel Macros - VBA

Need Excel Macro to do the following:

My sample data looks like:

Every RAW File:

1. The cell values and no. of rows will change for every time i want to use the macro. The no. of rows will vary from 2 to 500.

2. However the no. of columns, position of column, header of columns will be same for every raw file.

Macro to do the following:

*** Function A ***

A:. change cell value for column K based on the cell value of column A.

a. If Cell A2 ends in DS01, then multiply the value in cell K2 by 2.

b. If cell A2 ends in DS02, then multiply the value K2 by 4

c. If not match found in A2, then check next row (cell B2) until all the rows (ie. from A2 to A500) in column A are verified.

d. After all the rows are verified for function 1a to 1c, then do function B below:

*** FUNCTION B ***

a. Remove characters DS00, DS01, DS03 if it exists any cell range A2 to A500 (eg 1. After Function A above, the cell value A2 is MWRB6446-DS00, then change cell A2 to read WRB6446 ), eg 2, if the cell value A2 is MWRB6452-DS00 then change cell A2 to read WRB6452. Then step 2b below

b. Check cell range A2 to A 500 for step Fucntion 2a. above.

*** FUNCTION C ***

1. If any cells from range A2 to A500 has the cell starting with 4, then remove the character 4 from any cell in column A. (Eg. if cell A2 reads 4WRB5246, then change cell A2 to WRB5246) eg 2: if cell A2 reads 4WRB5444, then change cell A2 to WRB5444. Then do step the next step below.

2. Check cell range A2 to A 500 for step C.1 above

*** FUNCTION D **

1. If any cells from range J2 to J500 equals to “UPS 3 Day Select” OR “UPS 2nd Day Air” (eg. Matched cell J2 ) then check for column H for the same row (eg. Check Row 2 as J2 had matched) to check:

a. equals to any of following 2 characters : NY, CT, NJ,OH, PA, DC, VA, KY,DE,MD,MI,IL,WV,TN,AR,MS,AL,LA THEN change value in cell of column J to read “UPS Ground” (eg. cell J2 had matched to “UPS 3 Day Select” in step D.1 above, then if H2 matches to “CT”, then change value of J2 to read “UPS Ground”)

b. If no match found, then check cell J3….and so on until all the rows in column J are checked.

2. If any cells from range J2 to J500 equals to “UPS Next Day Air” (eg. Matched cell J2 ) then check for column H for the same row to check:

a. equals to “NC” or “VA” THEN change value in cell J to read “UPS Ground” (eg. cell J2 had matched to “UPS Next Day Air” in step D.2 above, then if H2 matches to “NC”, then change value of J2 to read “UPS Ground”)

b. If no match found, then check cell J3….and so on until all the rows in column J are checked.

3. If any cells from range J2 to J500 equals to “UPS 3 Day Select” (eg. Matched cell J2 ) then check for column H for the same row to check:

a. equals to any of following 2 characters : ME, VT, NH,MA, RI, WI, MN, IA, KS, OK, TX THEN change value in cell J to read “UPS Ground” (eg. cell J2 had matched to “UPS 3 Day Select” in step D.2 above, then if H2 matches to “NC”, then change value of J2 to read “UPS Ground”)

b. If no match found, then check cell J3….and so on until all the rows in column J are checked.

Attached File has updated specs of requirements. Please read before quote.

Færdigheder: PHP, Software Arkitektur, Visual Basic

Se mere: writing to read, writing to a file in c, writing next, writing macros in excel, writing macros, writing excel macros, writing characters, writing a check, vba with, starting with visual basic, n equals, nc time, me excel, md b file, found writing, check writing, check my writing, character writing, ar position, vba time, vba for, vba and macros, me vba, md n, in vba

Om arbejdsgiveren:
( 6 bedømmelser ) Jacksonville, United States

Projekt-ID: #990758