Nhờ viêt giúp VBA tạo cột phụ chứa giá trị của công thức (1 người xem)

[longlt08]

Xin chào ACE trên diễn đàn !
Nhờ mọi người viết giúp đoạn code VBA để khi rời khỏi sheet, excel tự động kiểm tra giá trị trong cột, nếu giá trị trong cột là trống (blank) thò điền giá trị 0 vào cột phụ. Nếu ô của cột đó có giá trị >0 thì điền giá trị đó vào cột phụ. Xin chân thành cảm ơn !
có file gửi kèm theo

 

[dhn46]

Bạn tham khảo Code này

Private Sub Worksheet_Deactivate()
Dim cls As Range
For Each cls In Range("c3:c" & Range("b65536").End(xlUp).Row)
    If cls = "" Then
        cls.Offset(, 1) = 0
    Else
        cls.Offset(, 1) = cls
    End If
Next
End Sub

 

[nghiaphuc]

Xin chào ACE trên diễn đàn !
Nhờ mọi người viết giúp đoạn code VBA để khi rời khỏi sheet, excel tự động kiểm tra giá trị trong cột, nếu giá trị trong cột là trống (blank) thò điền giá trị 0 vào cột phụ. Nếu ô của cột đó có giá trị >0 thì điền giá trị đó vào cột phụ. Xin chân thành cảm ơn !
có file gửi kèm theo

Thế này chẳng hạn:

Private Sub Worksheet_Deactivate()
    With Range("E3:E" & [B65536].End(xlUp).Row)
        .FormulaR1C1 = "=RC[-2]"
        .Value = .Value
    End With
End Sub

 

[longlt08]

Xin cảm ơn các bạn đã giúp đỡ. Mình thử đoạn VBA của dhn46 thì chạy tốt đúng theo ý của mình. Đoạn code của nghiaphuc chưa chạy đúng.

 

[nghiaphuc]

Xin cảm ơn các bạn đã giúp đỡ. Mình thử đoạn VBA của dhn46 thì chạy tốt đúng theo ý của mình. Đoạn code của nghiaphuc chưa chạy đúng.

Cụ thể chưa đúng là ra sao nhỉ?! Mình đã test kỹ rồi mới đưa lên đấy.
Đoạn code trên thực hiện 2 việc: Gán công thức để lấy giá trị trên cột C. Sau đó Paste Values vào chính vùng vừa gán công thức.
Bạn mô tả kết quả thu được sau khi chạy code xem sao.
-----------------------------------------------------------
À, mình hiểu rồi. Tại mình gán giá trị vào cột E chứ không phải cột D như trong yêu cầu. Do hiểu nhầm ý một chút.
Bạn sửa lại như vầy là được:

Private Sub Worksheet_Deactivate()
    With Range("[COLOR=#ff0000][B]D[/B][/COLOR]3:[COLOR=#ff0000][B]D[/B][/COLOR]" & [B65536].End(xlUp).Row)
        .FormulaR1C1 = "=RC[-[COLOR=#ff0000][B]1[/B][/COLOR]]"
        .Value = .Value
    End With
End Sub

Cũng xin nói thêm là với code của dhn46, nếu ô cuối cùng trên cột C (cụ thể trong file là ô C9) không có giá trị thì ở cột phụ sẽ không có kết quả như ý.

 

[Quang_Hải]

Cụ thể chưa đúng là ra sao nhỉ?! Mình đã test kỹ rồi mới đưa lên đấy.
Đoạn code trên thực hiện 2 việc: Gán công thức để lấy giá trị trên cột C. Sau đó Paste Values vào chính vùng vừa gán công thức.
Bạn mô tả kết quả thu được sau khi chạy code xem sao.

Chắc chắn là code của NghiaPhuc không sai, nhưng vì cái [B65536].End(XlUp).Row đấy mà. Tác giả chắc xóa mất dữ liệu của cột B rồi. Đoán thế đấy.

 

[dhn46]

Code cuả anh NghĩaPhuc không vấn đề gì cả chỉ có điều hiểu sai vị trí bạn muốn paste thôi, bạn muốn cột D còn anh xử lý cho cột E. Sửa lại chút là ổn

Private Sub Worksheet_Deactivate()
    With Range("[COLOR=#ff0000]D3:D[/COLOR]" & [B65536].End(xlUp).Row)
        .FormulaR1C1 = "=RC[[COLOR=#ff0000]-1[/COLOR]]"
        .Value = .Value
    End With
End Sub

 

[Ba Tê]

Xin cảm ơn các bạn đã giúp đỡ. Mình thử đoạn VBA của dhn46 thì chạy tốt đúng theo ý của mình. Đoạn code của nghiaphuc chưa chạy đúng.

Code ở bài #2: Nếu cột B có 100 dòng, cột C chỉ có số liệu đến dòng thứ 10 thì chỉ ghi số liệu sang cột D từ dòng 1-10, từ dòng 11 đến 100 không ghi, do xác định vùng dữ liệu dựa vào cột C.
Code của Nghiaphuc không thấy sai chỗ nào? Chỉ là nhập công thức rồi gán giá trị trở lại.
Thêm 1 cách gán trực tiếp, kết quả giống như của Nghĩa Phúc, không giống bài #2.

Private Sub Worksheet_Deactivate()
Dim ARg(), Akq(1 To 65000, 1 To 1), I As Long
ARg = Range([B3], [B65000].End(xlUp)).Resize(, 2)
For I = 1 To UBound(ARg, 1)
    Akq(I, 1) = ARg(I, 2) * 1
Next I
[D3].Resize(I - 1).Value = Akq
End Sub

 

[Quang_Hải]

Code ở bài #2: Nếu cột B có 100 dòng, cột C chỉ có số liệu đến dòng thứ 10 thì chỉ ghi số liệu sang cột D từ dòng 1-10, từ dòng 11 đến 100 không ghi, do xác định vùng dữ liệu dựa vào cột C.
Code của Nghiaphuc không thấy sai chỗ nào? Chỉ là nhập công thức rồi gán giá trị trở lại.
Thêm 1 cách gán trực tiếp, kết quả giống như của Nghĩa Phúc, không giống bài #2.

Private Sub Worksheet_Deactivate()
Dim ARg(), Akq(1 To 65000, 1 To 1), I As Long
ARg = Range([B3], [B65000].End(xlUp)).Resize(, 2)
For I = 1 To UBound(ARg, 1)
    Akq(I, 1) = ARg(I, 2) * 1
Next I
[D3].Resize(I - 1).Value = Akq
End Sub

Anh ơi khai báo chi nhiều mảng vậy? Một em là được rồi.
Lâu rồi không thấy anh Cò thọt cù lét anh, hôm nay em thọt cái nghen.

Em nghĩ là nếu dữ liệu nhiều code của NghiaPhuc sẽ chạy nhanh hơn vòng lặp

Private Sub Worksheet_Deactivate()
Dim ARg(), I As Long
ARg = Range([B3], [B65000].End(xlUp)).Resize(, 3)
For I = 1 To UBound(ARg, 1)
    ARg(I, 3) = ARg(I, 2) * 1
Next I
[B3].Resize(I - 1, 3).Value = ARg
End Sub

 

[Ba Tê]

Anh ơi khai báo chi nhiều mảng vậy? Một em là được rồi.
Lâu rồi không thấy anh Cò thọt cù lét anh, hôm nay em thọt cái nghen.

Em nghĩ là nếu dữ liệu nhiều code của NghiaPhuc sẽ chạy nhanh hơn vòng lặp

Private Sub Worksheet_Deactivate()
Dim ARg(), I As Long
ARg = Range([B3], [B65000].End(xlUp)).Resize(, 3)
For I = 1 To UBound(ARg, 1)
    ARg(I, 3) = ARg(I, 2) * 1
Next I
[B3].Resize(I - 1, 3).Value = ARg
End Sub

Thọt đúng chỗ nhột thiệt nghe.
Ec..

 

[TrungChinhs]

Xin chào ACE trên diễn đàn !
Nhờ mọi người viết giúp đoạn code VBA để khi rời khỏi sheet, excel tự động kiểm tra giá trị trong cột, nếu giá trị trong cột là trống (blank) thò điền giá trị 0 vào cột phụ. Nếu ô của cột đó có giá trị >0 thì điền giá trị đó vào cột phụ. Xin chân thành cảm ơn !
có file gửi kèm theo

Sao không đặt và giải quyết vấn đề là copy cột C ra cột phụ ? để thay cho "tự động kiểm tra giá trị trong cột, nếu giá trị trong cột là trống (blank) thò điền giá trị 0 vào cột phụ. Nếu ô của cột đó có giá trị >0 thì điền giá trị đó vào cột phụ."

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Range([b3], [b65000].End(3)).Offset(, 1).Copy [e3]
End Sub

 

[ndu96081631]

Anh ơi khai báo chi nhiều mảng vậy? Một em là được rồi.
Lâu rồi không thấy anh Cò thọt cù lét anh, hôm nay em thọt cái nghen.

Em nghĩ là nếu dữ liệu nhiều code của NghiaPhuc sẽ chạy nhanh hơn vòng lặp

Private Sub Worksheet_Deactivate()
Dim ARg(), I As Long
ARg = Range([B3], [B65000].End(xlUp)).Resize(, 3)
For I = 1 To UBound(ARg, 1)
    ARg(I, 3) = ARg(I, 2) * 1
Next I
[B3].Resize(I - 1, 3).Value = ARg
End Sub

Nếu nó hổng phải là number thì *1 cũng "đi Pháp" ở luôn
Thành ra, cứ IF bình thương đi cho chắc ăn

 

[Quang_Hải]

Nếu nó hổng phải là number thì *1 cũng "đi Pháp" ở luôn
Thành ra, cứ IF bình thương đi cho chắc ăn

Nếu vậy thì mình +0 thì không lỗi, nhưng em vẫn khoái cách của NghiaPhuc.

 

[Excel Công Cụ Tuyệt Vời Của Bạn]

Em xin dùng thử ADO học lóm từ web này như sau:

Private Sub Worksheet_Deactivate()

Dim lsSQL As String, cnn As Object, lrs As Object
Set cnn = CreateObject("ADODB.Connection")
Set lrs = CreateObject("ADODB.Recordset")
With cnn
    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & ThisWorkbook.FullName & _
                        ";Extended Properties=""Excel 8.0;HDR=No;"";"
    .Open
End With
    lsSQL = "SELECT F1,  IIF(ISNULL([F1]),0,[F1]) FROM [TH$C3:D9]"
    lrs.Open lsSQL, cnn, 3, 1
With Sheet1
   .[D3:D9].ClearContents
   .[C3].CopyFromRecordset lrs
End With
lrs.Close: Set lrs = Nothing
cnn.Close: Set cnn = Nothing


End Sub

 

[ndu96081631]

Nếu vậy thì mình +0 thì không lỗi, nhưng em vẫn khoái cách của NghiaPhuc.

Cũng tuỳ!
Dữ liệu ít, làm bằng cách gì cũng được, còn lại thì phương pháp mảng vẫn luôn khẳng định được ưu thế về tốc độ

 

[longlt08]

Chào cả nhà !
Tôi định thêm dòng (.clear) trong đoạn VBA (để xóa số liệu ở cột D, trước khi cộng thêm số liệu) nhưng thấy không có tác dụng
With Range("D3" & [B65536].End(xlUp).Row)
.clear
.FormulaR1C1 = "=RC[-1]"
.Value = .Value
End With
Nhờ mọi người giúp đỡ thêm. Xin cảm ơn !

 

[nghiaphuc]

Chào cả nhà !
Tôi định thêm dòng (.clear) trong đoạn VBA (để xóa số liệu ở cột D, trước khi cộng thêm số liệu) nhưng thấy không có tác dụng
With Range("D3" & [B65536].End(xlUp).Row)
.clear
.FormulaR1C1 = "=RC[-1]"
.Value = .Value
End With
Nhờ mọi người giúp đỡ thêm. Xin cảm ơn !

Tại sao lại phải Clear nhỉ? Việc này đâu có ý nghĩa gì đâu, bởi vì có xóa hay không thì công câu lệnh .FormulaR1C1 = "=RC[-1]" cũng sẽ lấy số liệu mới mà.
À, mà tại sao lại có chuyện "cộng thêm số liệu" ở đây nhỉ? Hay bạn lại có ý tưởng gì khác?

 

[longlt08]

Thực ra mình thêm cột phụ để giải quyết việc lọc cột có ô blank (không có dữ liệu- dùng cho pivottable, trong PV nếu bỏ dấu blank thì kết quả của các cột giá trị khác bị thay đổi). Mình đã áp dụng CT của nghiaphuc thấy cho kết quả như mong muốn nhưng sau đó không hiểu sao có một số ô có giá trị không đúng nên phải xoá thủ công (!) *Bảng dữ liệu của mình có khoảng vài ngàn dòng. Mình muốn trước khi ghi số liệu vào cột phụ thì cứ xoá đi cho sạch rồi mới cộng số liệu vào cột phụ. Đó là lý do của mình muốn thêm dòng (.clear). Mọi người giúp thêm mình với nhé. Xin cảm ơn mọi người !

 

[Quang_Hải]

Thực ra mình thêm cột phụ để giải quyết việc lọc cột có ô blank (không có dữ liệu- dùng cho pivottable, trong PV nếu bỏ dấu blank thì kết quả của các cột giá trị khác bị thay đổi). Mình đã áp dụng CT của nghiaphuc thấy cho kết quả như mong muốn nhưng sau đó không hiểu sao có một số ô có giá trị không đúng nên phải xoá thủ công (!) *Bảng dữ liệu của mình có khoảng vài ngàn dòng. Mình muốn trước khi ghi số liệu vào cột phụ thì cứ xoá đi cho sạch rồi mới cộng số liệu vào cột phụ. Đó là lý do của mình muốn thêm dòng (.clear). Mọi người giúp thêm mình với nhé. Xin cảm ơn mọi người !

Thêm dòng lênh .Clear đó đã đúng cú pháp rồi. Bạn cần thế nào?

 

[longlt08]

Xin cảm ơn quanghai !
Mình kiểm tra lại cú pháp của câu lệnh VBA là đúng nhưng không hiểu sao khi thực thi trong file dữ liệu của mình thì "có vấn đề". Cũng y chang như vậy nhưng lại không có kết quả. Mình đang phải kiểm tra lại tổng thể. Xin chào bạn !