Excel List Sorter
 

I done this using formulas in Excel and works fine. However, I'm
looking to easing myself into broadening my VBA knowledge. Wonder if
this is possible in Excel VBA?

I have a list(table 1) and I want to generate using Excel VBA the
remaining part of the table using excel VBA (see table 2).

Want some excel vba code to go through each record Payment field column
and fill in the Pay_Type, Area and Interest columns. Taking into
account that A_ID column is always blank.

The key for the Payment is:
Pay_Type Area Interest
M = Monthly A 10
S = Semi-Ann 3 30
Q = Quarterly DDE 25
D = Decade 10y 4
Y = Yearly 123 5

table1:
Customer Payment
1 M
20 M
17 S
3 M
4 S
15 S
5 S
70 Q
81 Y
9 Q
10 Q
34 Y
21 Y
6 Q
9 M

table 2
Customer Payment A_ID Pay_Type Area Interest
1 M Monthly A 10
20 M Monthly A 10
17 S Semi-Ann 3 30
3 M Monthly A 10
4 S Semi-Ann 3 3
15 S Semi-Ann 3 3
5 S Semi-Ann 3 3
70 Q Quarterly DDE 25
81 Y Yearly 123 5
92 Q Quarterly DDE 25
11 Q Quarterly DDE 25
34 Y Yearly 123 5
21 Y Yearly 123 5
6 Q Quarterly DDE 25
9 M Monthly A 10
10 S Semi-Ann 3 30
40 D Decade 10y 4
23 D Decade 10y 4

Is this doable in Excel VBA?

Apreciate your help on this matter.

Many Thanks and Kind regards

Brenda "XXX"

Excel List Sorter
 

Try it without VBA using Vlookup on Table 3 which is your legend

it is a nested Vlookup
first customer number gives payment code
then payment code gives details for that code

put formulas in columns 2,3,4,5 of table 2

type in customer number in col 1 and other columns fill themselves

Formulas are easier and faster than code, when they work..

" wrote:

I done this using formulas in Excel and works fine. However, I'm
looking to easing myself into broadening my VBA knowledge. Wonder if
this is possible in Excel VBA?

I have a list(table 1) and I want to generate using Excel VBA the
remaining part of the table using excel VBA (see table 2).

Want some excel vba code to go through each record Payment field column
and fill in the Pay_Type, Area and Interest columns. Taking into
account that A_ID column is always blank.

The key for the Payment is:
Pay_Type Area Interest
M = Monthly A 10
S = Semi-Ann 3 30
Q = Quarterly DDE 25
D = Decade 10y 4
Y = Yearly 123 5

table1:
Customer Payment
1 M
20 M
17 S
3 M
4 S
15 S
5 S
70 Q
81 Y
9 Q
10 Q
34 Y
21 Y
6 Q
9 M

table 2
Customer Payment A_ID Pay_Type Area Interest
1 M Monthly A 10
20 M Monthly A 10
17 S Semi-Ann 3 30
3 M Monthly A 10
4 S Semi-Ann 3 3
15 S Semi-Ann 3 3
5 S Semi-Ann 3 3
70 Q Quarterly DDE 25
81 Y Yearly 123 5
92 Q Quarterly DDE 25
11 Q Quarterly DDE 25
34 Y Yearly 123 5
21 Y Yearly 123 5
6 Q Quarterly DDE 25
9 M Monthly A 10
10 S Semi-Ann 3 30
40 D Decade 10y 4
23 D Decade 10y 4

Is this doable in Excel VBA?

Apreciate your help on this matter.

Many Thanks and Kind regards

Brenda "XXX"

Excel List Sorter
 

Ok , read your post again....
read my first post- when you can use formulas do so


For VBA this would call
planning how you want to launch the VBA
Error checking
Looping through each cell in column
use of Select Case for payment type
writing to sheet using offset

Which part do you need help with?

" wrote:

I done this using formulas in Excel and works fine. However, I'm
looking to easing myself into broadening my VBA knowledge. Wonder if
this is possible in Excel VBA?

I have a list(table 1) and I want to generate using Excel VBA the
remaining part of the table using excel VBA (see table 2).

Want some excel vba code to go through each record Payment field column
and fill in the Pay_Type, Area and Interest columns. Taking into
account that A_ID column is always blank.

The key for the Payment is:
Pay_Type Area Interest
M = Monthly A 10
S = Semi-Ann 3 30
Q = Quarterly DDE 25
D = Decade 10y 4
Y = Yearly 123 5

table1:
Customer Payment
1 M
20 M
17 S
3 M
4 S
15 S
5 S
70 Q
81 Y
9 Q
10 Q
34 Y
21 Y
6 Q
9 M

table 2
Customer Payment A_ID Pay_Type Area Interest
1 M Monthly A 10
20 M Monthly A 10
17 S Semi-Ann 3 30
3 M Monthly A 10
4 S Semi-Ann 3 3
15 S Semi-Ann 3 3
5 S Semi-Ann 3 3
70 Q Quarterly DDE 25
81 Y Yearly 123 5
92 Q Quarterly DDE 25
11 Q Quarterly DDE 25
34 Y Yearly 123 5
21 Y Yearly 123 5
6 Q Quarterly DDE 25
9 M Monthly A 10
10 S Semi-Ann 3 30
40 D Decade 10y 4
23 D Decade 10y 4

Is this doable in Excel VBA?

Apreciate your help on this matter.

Many Thanks and Kind regards

Brenda "XXX"

Excel List Sorter
 

Option Base 0

Sub ProcData()
Dim v as Variant
Dim v1 as Variant
Dim rng as Range
Dim cell as Range
Dim res as Variant
v1 = array("M","S","Q","D","Y")
Redim v(0 to 4)
v(0) = Array("Monthly","A",10)
v(1) = Array("Semi-Ann","3", 30)
v(2) = Array("Quarterly","DDE",25)
v(3) = Array("Decade","10y",4)
v(4) = Array("Yearly","123",5)
set rng = Range(Cells(2,2),Cells(2,2).End(xldown))
for each cell in rng
res = application.Match(cell.Value,v1,0)
if not iserror(res) then
cell.offset(0,2).Resize(1,3).Value = v(res+1)
end if
Next
End Sub

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
I done this using formulas in Excel and works fine. However, I'm
looking to easing myself into broadening my VBA knowledge. Wonder if
this is possible in Excel VBA?

I have a list(table 1) and I want to generate using Excel VBA the
remaining part of the table using excel VBA (see table 2).

Want some excel vba code to go through each record Payment field column
and fill in the Pay_Type, Area and Interest columns. Taking into
account that A_ID column is always blank.

The key for the Payment is:
Pay_Type Area Interest
M = Monthly A 10
S = Semi-Ann 3 30
Q = Quarterly DDE 25
D = Decade 10y 4
Y = Yearly 123 5

table1:
Customer Payment
1 M
20 M
17 S
3 M
4 S
15 S
5 S
70 Q
81 Y
9 Q
10 Q
34 Y
21 Y
6 Q
9 M

table 2
Customer Payment A_ID Pay_Type Area Interest
1 M Monthly A 10
20 M Monthly A 10
17 S Semi-Ann 3 30
3 M Monthly A 10
4 S Semi-Ann 3 3
15 S Semi-Ann 3 3
5 S Semi-Ann 3 3
70 Q Quarterly DDE 25
81 Y Yearly 123 5
92 Q Quarterly DDE 25
11 Q Quarterly DDE 25
34 Y Yearly 123 5
21 Y Yearly 123 5
6 Q Quarterly DDE 25
9 M Monthly A 10
10 S Semi-Ann 3 30
40 D Decade 10y 4
23 D Decade 10y 4

Is this doable in Excel VBA?

Apreciate your help on this matter.

Many Thanks and Kind regards

Brenda "XXX"

Excel List Sorter
 

Tom,
Thanks for your help... Still unable to see the VBA code work
correctly.

Having run the VBA code provided, it generated different results from
(table2) and also a VBA error occured on line:
cell.Offset(0, 2).Resize(1, 3).Value = v(res + 1)

Generated results are incorrect on the below table.
Customer Payment A_ID Pay_Type Area Interest
1 M Quarterly DDE 25
20 M Quarterly DDE 25
17 S Decade 10y 4
3 M Quarterly DDE 25
4 S Decade 10y 4
15 S Decade 10y 4
5 S Decade 10y 4
70 Q Yearly 123 5
81 Y
9 Q
10 Q
34 Y
21 Y
6 Q
9 M

I understand that formulas are faster and easier to use in Excel, but I
have some really big formulas in my spreadsheet and this is making my
excel spreadsheet very complex to audit through when I come to
documentating each formulas.. Would like the initail phrase to be VBA,
so reduce the file size and improve the calculation speed of the other
formulas.

Please help.

Appreciate your efforts given so far...

Kind regards

Brenda Dancer 'xxx'

Excel List Sorter
 

I had a typo in that line, It should have been minus instead of plus. I
also assume you data starts in row 2 and the first row contains headers:


Option Base 0

Sub ProcData()
Dim v As Variant
Dim v1 As Variant
Dim rng As Range
Dim cell As Range
Dim res As Variant
v1 = Array("M", "S", "Q", "D", "Y")
ReDim v(0 To 4)
v(0) = Array("Monthly", "'A", 10)
v(1) = Array("Semi-Ann", "'3", 30)
v(2) = Array("Quarterly", "'DDE", 25)
v(3) = Array("Decade", "'10y", 4)
v(4) = Array("Yearly", "'123", 5)
Set rng = Range(Cells(2, 2), Cells(2, 2).End(xlDown))
For Each cell In rng
res = Application.Match(cell.Value, v1, 0)
If Not IsError(res) Then
cell.Offset(0, 2).Resize(1, 3).Value = v(res - 1)
End If
Next
End Sub

if it starts in row1 change
Set rng = Range(Cells(2, 2), Cells(2, 2).End(xlDown))

to

Set rng = Range(Cells(1, 2), Cells(2, 2).End(xlDown))

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
Tom,
Thanks for your help... Still unable to see the VBA code work
correctly.

Having run the VBA code provided, it generated different results from
(table2) and also a VBA error occured on line:
cell.Offset(0, 2).Resize(1, 3).Value = v(res + 1)

Generated results are incorrect on the below table.
Customer Payment A_ID Pay_Type Area Interest
1 M Quarterly DDE 25
20 M Quarterly DDE 25
17 S Decade 10y 4
3 M Quarterly DDE 25
4 S Decade 10y 4
15 S Decade 10y 4
5 S Decade 10y 4
70 Q Yearly 123 5
81 Y
9 Q
10 Q
34 Y
21 Y
6 Q
9 M

I understand that formulas are faster and easier to use in Excel, but I
have some really big formulas in my spreadsheet and this is making my
excel spreadsheet very complex to audit through when I come to
documentating each formulas.. Would like the initail phrase to be VBA,
so reduce the file size and improve the calculation speed of the other
formulas.

Please help.

Appreciate your efforts given so far...

Kind regards

Brenda Dancer 'xxx'

Excel List Sorter
 

Are absolute and relative referencing doable in VBA arrays?

Modified the code above and now having problems will formulas in VBA
arrays.
Is it possible to put formulas in VBA arrays and make the cells
absolute and relative in the formulas.
_________________
For example: Need to $e4 to cell to be relative, so it will change row
number each time and $F$1 to be absolute. This is in relation to the
code below. I've tried inserting rc[5] for the relative array value,
but this does not work for the Sub ProcData() macro (see below)

The Array.......
v(0) = Array("Monthly", "=($e4,""dd"")&TEXT($F$1,""-mm-yy""))", 10)

_______________________



Option Base 0


Sub ProcData()
Dim v As Variant
Dim v1 As Variant
Dim rng As Range
Dim cell As Range
Dim res As Variant
v1 = Array("M", "S", "Q", "D", "Y")
ReDim v(0 To 4)
v(0) = Array("Monthly", "=(e4,""dd"")&TEXT($F$1,""-mm-yy""))", 10)
v(1) = Array("Semi-Ann", "'3", 30)
v(2) = Array("Quarterly", "'DDE", 25)
v(3) = Array("Decade", "'10y", 4)
v(4) = Array("Yearly", "'123", 5)
Set rng = Range(Cells(3, 4), Cells(3, 4).End(xlDown))
For Each cell In rng
res = Application.Match(cell.Value, v1, 0)
If Not IsError(res) Then
cell.Offset(0, 3).Resize(1, 3).Value = v(res - 1)
End If
Next
End Sub



Thanks for any help given.

Brenda Dancer "xxxx"

Excel List Sorter
 

Sorry the correct code.

Are absolute and relative referencing doable in VBA arrays?

Modified the code above and now having problems will formulas in VBA
arrays.
Is it possible to put formulas in VBA arrays and make the cells
absolute and relative in the formulas.
_________________
For example: Need to $e4 to cell to be relative, so it will change row
number each time and $F$1 to be absolute. This is in relation to the
code below. I've tried inserting rc[5] for the relative array value,
but this does not work for the Sub ProcData() macro (see below)


The Array.......
v(0) = Array("Monthly", "=(Text(e4,""dd"")&TEXT($F$1,""-mm-yy""))", 10)


_______________________


Sub ProcData()
Dim v As Variant
Dim v1 As Variant
Dim rng As Range
Dim cell As Range
Dim res As Variant
v1 = Array("M", "S", "Q", "D", "Y")
ReDim v(0 To 4)
v(0) = Array("Monthly", "=(Text(e4,""dd"")&TEXT($F$1,""-mm-yy""))", 10)
v(1) = Array("Semi-Ann", "'3", 30)
v(2) = Array("Quarterly", "'DDE", 25)
v(3) = Array("Decade", "'10y", 4)
v(4) = Array("Yearly", "'123", 5)
Set rng = Range(Cells(3, 4), Cells(3, 4).End(xlDown))
For Each cell In rng
res = Application.Match(cell.Value, v1, 0)
If Not IsError(res) Then
cell.Offset(0, 3).Resize(1, 3).Value = v(res - 1)
End If
Next
End Sub



Thanks for any help given.


Brenda Dancer "xxxx"