vba，只有在空白的情况下，单元格的公式到数据的底部

发布于 2025-02-11 11:37:04 字数 795 浏览 0 评论 0原文

我创建了以下公式，但是它无法正常工作。 G列中的一些单元格有数字，另一些则没有。我只希望该公式出现在空白单元的G列中。

g =佣金率（在原始数据下载，佣金费率仅在此处显示，当它们是合同期限的静态利率时） V =日期签署了协议 AA列：AF =一年达成协议的佣金利率（AA = 1年，AB = 2年级），

我想做的就是将正确的可变佣金利率添加到静态佣金率所在的同一列中。

Sub Clean_Data()
Dim y As Range
lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row

Set X = Range("G3:G" & lr)
For Each y In X.Cells

    If y = "" Then
        y = Formula = "=IF(AND(((TODAY()-V3)/365)>=0,((TODAY()-V3)/365)<=1),AA3,IF(AND(((TODAY()-V3)/365)>1,((TODAY()-V3)/365)<=2),AB3,IF(AND(((TODAY()-V3)/365)>2,((TODAY()-V3)/365)<=3),AC3,IF(AND(((TODAY()-V3)/365)>3,((TODAY()-V3)/365)<=4),AD3,IF(AND(((TODAY()-V3)/365)>4,((TODAY()-V3)/365)<=5),AE3,AF3)))))"
    End If
    
Next y
End Sub

原文

I have the below formula created, but it is not working properly. Some of the cells in column G have numbers, others do not. I just want this formula to appear in column G for the blank cells.

Column G = commission rate (on the raw data download, commission rates are only showing here if they are a static rate for the duration of contract)
Column V = date an agreement was signed
Column AA:AF = commission rates by year into agreement (AA = year 1, AB = year 2, so on)

What I am trying to do is add the correct variable commission rate to the same column the static commission rates are on.

Sub Clean_Data()
Dim y As Range
lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row

Set X = Range("G3:G" & lr)
For Each y In X.Cells

    If y = "" Then
        y = Formula = "=IF(AND(((TODAY()-V3)/365)>=0,((TODAY()-V3)/365)<=1),AA3,IF(AND(((TODAY()-V3)/365)>1,((TODAY()-V3)/365)<=2),AB3,IF(AND(((TODAY()-V3)/365)>2,((TODAY()-V3)/365)<=3),AC3,IF(AND(((TODAY()-V3)/365)>3,((TODAY()-V3)/365)<=4),AD3,IF(AND(((TODAY()-V3)/365)>4,((TODAY()-V3)/365)<=5),AE3,AF3)))))"
    End If
    
Next y
End Sub

分享到QQ

分享到微博