PDA

View Full Version : Sleeper: Changing the current macro using if Statement or offset



hakunamatata
09-09-2012, 05:29 AM
Hello Everybody
The macro attached with this workbook works fine, i just want to add the condition with this macro, actually the code which is attached with this macro just compare Student ID of Worksheet 1 with Student ID of Worksheet 2 and when Student ID found then it writes the compared Student ID and respective columns in Worksheet 3. I just want to put the condition which is that if the Column A in Worksheet1 means ID Name is Multiple - 123(Choice) then it write the Pass year 2 in the result against the compared ID otherwise it writes the Pass year 1 against the compared ID in worksheet 3.

hakunamatata
09-09-2012, 06:46 AM
somebody, please help me in this regard. i really need it badly.

snb
09-09-2012, 10:11 AM
Sub snb()
With Sheets("sheet2").Cells(11, 1).CurrentRegion
sn = .Offset(1).Resize(.Rows.Count - 1, 5)
End With
sp = Sheets("sheet1").Cells(3, 1).CurrentRegion
sq = Sheets("sheet1").Cells(3, 1).CurrentRegion.Columns(3)
For j = 1 To UBound(sn)
If Not IsError(Application.Match(sn(j, 1), sq, 0)) Then sn(j, 5) = sp(Application.Match(sn(j, 1), sq, 0), 16 + Abs(2 * (Left(sp(Application.Match(sn(j, 1), sq, 0), 1), 5) = "Multi")))
Next
Sheets("sheet3").Cells(30, 1).Resize(UBound(sn), UBound(sn, 2)) = sn
Sheets("sheet3").Cells(30, 1).Resize(UBound(sn), UBound(sn, 2)).Columns(5).SpecialCells(4).EntireRow.Delete
end sub


A clear explanation wouldn't have been inappropriate.

hakunamatata
09-09-2012, 11:06 AM
Hello,
thanks for your code. I think i could not clear you completely. Actually i want to change this block,


For i = 1 To UBound(a)
s = Trim(a(i, 3))
If Not .exists(s) Then
.Item(s) = VBA.Array(s, a(i, 11), a(i, 13), a(i, 16), a(i, 4), Empty)
Else
myArrayList.Add s, VBA.Array(s, a(i, 11), a(i, 13), a(i, 16), a(i, 4), Empty)
End If
Next

some thing like this, but this does not work



For i = 1 To UBound(a)
s = Trim(a(i, 3))
If Not .exists(s) Then
If a(i, 1) Like "Multiple - 123" Then .Item(s) = VBA.Array(s, a(i, 11), a(i, 13), a(i, 18), a(i, 4), Empty)
Elseif .Item(s) = VBA.Array(s, a(i, 11), a(i, 13), a(i, 16), a(i, 4), Empty)
Else If myArrayList.Add s, VBA.Array(s, a(i, 11), a(i, 13), a(i, 16), a(i, 4), Empty)
End If
Next

snb
09-09-2012, 02:17 PM
My code was meant as a replacement of yours.

Aussiebear
09-09-2012, 11:52 PM
[QUOTE=snb]
A clear explanation wouldn't have been inappropriate.[/QUOTE}

So does an Response, so people understand what you are providing.

snb
09-10-2012, 02:06 AM
Thanks for your helpful contribution.

Please supply....