Cách set một range có nhiều ngoại tệ thành range có một ngoại tệ (3 người xem)

[ST-Lu!]

Cách set một range có nhiều ngoại tệ thành range có một ngoại tệ???

Hi Các Anh, chị
Bình thường nếu một cột chỉ có một loại ngoại tệ em sẽ set range như sau

Set rDebit = myRange.Range(Cells(1, 2), Cells(mRow, 2))

Vị dụ: Chỉ có 2 loại ngoại tệ: tỷ giá quy đổi = 17,000 VNĐ/USD

Cột A: từ A2 --> A10 là giá trị ngoại tệ (như 100; 100,000; 0.15....)
Cột B: từ B2 ---> B10 là đơn vị ngoại tệ (như: USD; VNĐ; USD)

Bây giờ em muốn set range [A2:A10] quy về một loại ngoại tệ USD thì code VBA em viết như nào

Xin chỉ giáo

 

[sealand]

Theo thiển ý của mình nếu không có cột trung gian thì không thể set range được bởi vì range là đối tượng hiện hữu trên workbook. Trường hợp này bạn phải khai 1 mảng trung gian ví dụ tien(mRow) để nhận tiền quy đổi rồi sử lý hay sử lý trực tiếp từng dòng thôi.

 

[ST-Lu!]

Theo thiển ý của mình nếu không có cột trung gian thì không thể set range được bởi vì range là đối tượng hiện hữu trên workbook. Trường hợp này bạn phải khai 1 mảng trung gian ví dụ tien(mRow) để nhận tiền quy đổi rồi sử lý hay sử lý trực tiếp từng dòng thôi.

Như anh nói Range là một đối tượng hiện hữu trên workbook --> điều này không thể giống với name hả anh? --> thế thì phải tạo cột phụ rồi -- >hic
------------------------
Thay vì cột phụ em tạo một name như sau

=IF(OFFSET(P;;11)="VND";OFFSET(P;;9)/17000;OFFSET(P;;9))

Riêng với mảng động em đã làm ok ko cần dùng tới cột phụ, nhưng em đang tự hỏi khi viết code sẽ như nào?
Ở trên nếu tại cột 11 là VNĐ thì cột 9 sẽ /17,000 còn không cột 9 sẽ giữ nguyên

Anh xem giúp em VBA có làm được ko?

 

[sealand]

Mình không rành về Name lắm, ST_lu thử gửi file ví dụ về Name như bạn đặt như công thức để mình tham khảo với.Mình nạp tên tien như vậy và tại 1 ô trồng mình nhập =Sum(tien) nó không hiểu.

 

[ST-Lu!]

Cám ơn Anh nhiều
Trong file em đã chuyển đổi cột I, J --> về cùng một loại ngoại tệ USD qua 2 name
DbUSD và CrUSD

Anh xem hướng dẫn giúp em nhé

 

[hoangdanh282vn]

Bạn xem VD sau có đúng ý bạn không nha :

SUM(CrUsd) = 2,1809.7

và

Sub Test()
Dim Rw As Long, Arr() As Double
With Sheet1
Rw = .Range("cr").Rows.Count
ReDim Arr(1 To Rw)
For i = 6 To Rw + 5
    Arr(i - 5) = IIf(.Cells(i, 12) = "VND", .Cells(i, 10) / 17000, .Cells(i, 10))
Next
End With
MsgBox WorksheetFunction.Sum(Arr)  ' =>>2,1809.7
End Sub

 

[sealand]

Mình test trực tiếp trên file của ST và dùng sub sau để test:

Sub test()
Dim rg As Range
[COLOR=Red]Set rg = Sheet1.Range("Dbusd")[/COLOR]
tg = Application.WorksheetFunction.Sum(rg)
MsgBox tg
End Sub

Kết quả cho thấy rằng:

1/Code báo lỗi ngay dòng màu đỏ (Vùng Dbusd không nhận diện được)
2/Nếu đặt cột Debit với tên là DB và thay Dbusd bằng DB thì OK

Như vây, cách xác định Name của ST không áp dụng được trong trường hợp này.
Còn yêu cầu tiếp theo thì mình chưa rõ bạn định làm gì. Nếu chỉ gán vùng tên cho biến thì theo cú pháp dòng màu đỏ code trên. Đồng thời đặt lại tên vùng có tên DbUSD và CrUSD cho phù hợp.

 

[hoangdanh282vn]

Mình test trực tiếp trên file của ST và dùng sub sau để test:

Sub test()
Dim rg As Range
[COLOR=Red]Set rg = Sheet1.Range("Dbusd")[/COLOR]
tg = Application.WorksheetFunction.Sum(rg)
MsgBox tg
End Sub

Kết quả cho thấy rằng:

1/Code báo lỗi ngay dòng màu đỏ (Vùng Dbusd không nhận diện được)
2/Nếu đặt cột Debit với tên là DB và thay Dbusd bằng DB thì OK

Như vây, cách xác định Name của ST không áp dụng được trong trường hợp này.
Còn yêu cầu tiếp theo thì mình chưa rõ bạn định làm gì. Nếu chỉ gán vùng tên cho biến thì theo cú pháp dòng màu đỏ code trên. Đồng thời đặt lại tên vùng có tên DbUSD và CrUSD cho phù hợp.

hai Name CrUSD và DbUSD là hai Name trả vể 2 mảng động, đã là mảng thì làm sao ta có thể set nó thành Range được chứ, lỗi là đương nhiên.

Theo như ý của bạn ST thì bạn ấy muốn chuyển Name động dùng công thức sang VBA như thế nào. Như Code mình gửi ở trên, ta set cho nó thành Array , sau đó tình toán bình thường. Khi đó Mảng Arr() hay Name CrUSD cũng như nhau thôi.

 

[sealand]

To Hoang Danh: Nhưng nếu là Name bình thường thì ta vẫn set tốt . Trong file ví dụ kèm cũng là Name động đấy chứ.
Bạn ST_Lu là muốn gán vùng có tên chỉ định cho biến vùng cơ mà. Chứ còn gán cho array để sử lý như bạn chỉ là giải pháp cùng đường như bài trước mình đã nói (Vì đây không phải là ý muốn của ST_Lu). Thậm chí soát rồi sử lý luôn chả cần gán array() làm gì nữa.

 

[hoangdanh282vn]

To Hoang Danh: Nhưng nếu là Name bình thường thì ta vẫn set tốt chứ.

Dạ đúng rồi anh. Ở đây em chỉ nói đến 2 Name động trả về mảng sau khi dùng hàm If như file của bạn ST thôi ah

 

[ST-Lu!]

Bạn xem VD sau có đúng ý bạn không nha :

SUM(CrUsd) = 2,1809.7

và

Sub Test()
Dim Rw As Long, Arr() As Double
With Sheet1
Rw = .Range("cr").Rows.Count
ReDim Arr(1 To Rw)
For i = 6 To Rw + 5
    Arr(i - 5) = IIf(.Cells(i, 12) = "VND", .Cells(i, 10) / 17000, .Cells(i, 10))
Next
End With
MsgBox WorksheetFunction.Sum(Arr)  ' =>>2,1809.7
End Sub

Thầy Danh ơi
Mục đích của em là set range --> và sẽ dùng range này trong công thức tính toán
có phải trong code của anh ở trên Range set lại là Arr phải không?
Nhưng lúc này nó là Array thì phải?

 

[sealand]

ST_Lu à, cái bí là ở chỗ không set biến rng bằng array được, nếu không ta tạo mảng trung gian rồi set thì tốt.

 

[sealand]

Lọ mọ rồi mình cũng tìm được 1 cách set rng= nhưng nó có vẻ lôi thôi 1 chút.
Mình đặt 2 Name: VND và USD (Vấn đề là đặt Name cho nhiều vùng)

 

[ST-Lu!]

Lọ mọ rồi mình cũng tìm được 1 cách set rng= nhưng nó có vẻ lôi thôi 1 chút.
Mình đặt 2 Name: VND và USD (Vấn đề là đặt Name cho nhiều vùng)

   With Application.WorksheetFunction
   tg = .Sum(rg_us) + .Sum(rg_vn) / Sheet1.[d1]

Cám ơn Anh SeaLand rât nhiều về đoạn code trên nhưng thực ra vẫn chưa có một range (name) thực thụ mà mục đích của em là dùng name này để thực hiện công thức tính toán
ví dụ: em sẽ dùng với hàm Sumproduct chẳng hạn
Em sẽ không thể dùng tg ở trên được

Em muốn nó giống với cái name như CrUSD hay DbUSD ý
Em có thể sử dụng 2 name trên mà không không phải thêm cột phụ và tiện trong việc tính toán
Em nghĩ VBA cũng phải có giải pháp chứ nhỉ?

------
Em thấy dùng công thức đơn giản sao sang VBA phức tạp thế nhi?

 

[cadafi]

............... nhưng thực ra vẫn chưa có một range (name) thực thụ mà mục đích của em là dùng name này để thực hiện công thức tính toán
ví dụ: em sẽ dùng với hàm Sumproduct chẳng hạn 
Em sẽ không thể dùng [B]tg[/B] ở trên được
[/quote]
Ngay từ yêu cầu đầu tiên của bạn, thì bản chất của kết quả thu về đã không phải là một Range  (Thuộc tính Range) mà nó là một mảng các giá trị (Array) thỏa điều kiện.

Bạn cần nói rõ hơn hoặc đưa ra một ngữ cảnh rộng hơn một chút để từ đó có giải pháp tốt hơn!

Muốn thử thì vẫn cứ thử, bạn xem đoạn code này sẽ tạo ra 1 Name thỏa điều kiện bài 1 và có thể dùng Name này trong hàm Sumproduct được.
[quote][COLOR=Blue][B]Sub[/B][/COLOR] [COLOR=Black]Test[/COLOR]()
ActiveWorkbook.Names.Add Name:="Target", RefersTo:= _
"=(Sheet1!$B$2:$B$10=""USD"")*(Sheet1!$A$2:$A$10)+(Sheet1!$B$2:$B$10=""VND"")*(Sheet1!$A$2:$A$10/17000)"
[COLOR=Blue][B]End Sub[/B][/COLOR]
[/quote]

Sau đó quay ra excel, chạy SUB này.
Sau đó gõ công thức sau vào cell bất kỳ
[quote][B]=SUMPRODUCT(Target,--(Target>0))[/B][/quote]

 

[cadafi]

Lọ mọ rồi mình cũng tìm được 1 cách set rng= nhưng nó có vẻ lôi thôi 1 chút.
Mình đặt 2 Name: VND và USD (Vấn đề là đặt Name cho nhiều vùng)

To anh sealand: theo như kết quả trong code anh gửi, theo em, không nhất thiết phải code phức tạp đến vậy. Anh xem thử code sau xem sao:

Sub CongCadafi()
Dim Tg As Double
ActiveWorkbook.Names.Add Name:="Target", RefersTo:= _
"=(Sheet1!$B$2:$B$10=""USD"")*(Sheet1!$A$2:$A$10)+ (Sheet1!$B$2:$B$10=""VND"")*(Sheet1!$A$2:$A$10/17000)"
Tg = WorksheetFunction.Sum(Sheet1.[Target])
MsgBox Tg
End Sub

 

[ST-Lu!]

Dear Anh Kiệt
Hiện tại trong công thức em dùng là name động với các name
1. Name P (gốc - cột 1)

=INDIRECT("Sheet1!$A$6:$A$" & MATCH(REPT("z";255);Sheet1!$A:$A))

2. Cột thứ 8 (Debit) của em là một cột có 02 giá trị ngoại tệ (VND, USD), Cột 11 của em là đơn vị ngoại tệ
với Name ban đầu

Db =OFFSET(P;;8)

3. Em chuyển name trên về ngoại tệ
với name là DbUSD

=IF(OFFSET(P;;11)="VND";OFFSET(P;;8)/17000;OFFSET(P;;8))

Như vậy em sẽ dùng Name DbUSD này để tính toán ví dụ:

SUMPRODUCT(--(Invdate>=$B6)*(Invdate<=$C6)*Dbusd)-SUMPRODUCT(--(paydate=$C6)*(Invdate=$B6)*CrUsd)

Ý của em hỏi công thức thì em làm như vậy nhưng khi sang code thì làm như nào để có được DbUSD
Trong # 16 name của anh chưa phải là một name động theo kiểu như

Public Function OldOfDebt(mRange As Range, toDate As Date) As Double
Dim mRow As Long
mRow = mRange.Rows.Count
Set DbUSD = mRange.Range(Cells(1, 8), Cells(mRow, 8)) ' chỉ đối với một loại ngoại tệ thì em sẽ set như thế này

Cám ơn Anh chỉ giáo

 

[sealand]

To Ca_dafi: Code của bạn là chiêu khá hay đấy, mình tham khảo để dùng khi cần.
Nhưng Ca_dafi à, ST-Lu là muốn set biến rng cho cái vùng đó còn làm gì thì không biết. Mình đưa đoạn tính toán vào nhằm xác nhận kết quả set biến rng mà thôi.