ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

   
易厚学堂-专业的职场技能充电站 永久免费,网表让Excel秒变数据库 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel函数公式学习大典 高效办公必会的Office实战技巧 免费下载Excel行业应用视频
300集Office 2010微视频教程 Tableau-数据可视化工具 打造核心竞争力的职场宝典 13门Excel免费公开课任你学
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 免费的Excel考勤计算系统
查看: 455|回复: 10

[求助] VBA编辑错误,过程太大

[复制链接]

TA的精华主题

TA的得分主题

发表于 2018-5-16 12:04 | 显示全部楼层 |阅读模式
因代码过大,无法执行。
1、总行数70行,涉及3列
2、行数变动,列固定
哪位大师看编写代码,指导我一下,能够用for或其他语句循环,达到效果。谢谢

Sub 利趋结构比()
Dim pf2
Set pf2 = Sheets("利趋分析")    '定义对象,方便后面使用
    '根据题意进行赋值
    pf2.Cells(8, 5) = "100%"
    pf2.Cells(8, 6) = "100%"
    pf2.Cells(8, 7) = "0.00%"
    If pf2.Cells(8, 3) = "—" And pf2.Cells(9, 3) = "—" Then
        pf2.Cells(9, 5) = "—"
    ElseIf pf2.Cells(8, 3) = "—" And pf2.Cells(9, 3) <> "—" Then
        pf2.Cells(9, 5) = Round(pf2.Cells(9, 3) / 1, 2)
    ElseIf pf2.Cells(8, 3) <> "—" And pf2.Cells(9, 3) = "—" Then
        pf2.Cells(9, 5) = "—"
    Else
        pf2.Cells(9, 5) = Round(pf2.Cells(9, 3) / pf2.Cells(8, 3), 2)
    End If
    If pf2.Cells(8, 3) = "—" And pf2.Cells(9, 4) = "—" Then
        pf2.Cells(9, 6) = "—"
        ElseIf pf2.Cells(8, 3) = "—" And pf2.Cells(9, 4) <> "—" Then
            pf2.Cells(9, 6) = Round(pf2.Cells(9, 4) / 1, 2)
        ElseIf pf2.Cells(8, 3) <> "—" And pf2.Cells(9, 4) = "—" Then
            pf2.Cells(9, 6) = "—"
    Else
        pf2.Cells(9, 6) = Round(pf2.Cells(9, 4) / pf2.Cells(8, 3), 2)
    End If
    If pf2.Cells(9, 5) = "—" And pf2.Cells(9, 6) = "—" Then
        pf2.Cells(9, 7) = "—"
    ElseIf pf2.Cells(9, 5) = "—" And pf2.Cells(9, 6) <> "—" Then
        pf2.Cells(9, 7) = pf2.Cells(9, 6)
    ElseIf pf2.Cells(9, 5) <> "—" And pf2.Cells(9, 6) = "—" Then
        pf2.Cells(9, 7) = -pf2.Cells(9, 5)
    ElseIf pf2.Cells(9, 5) <> "—" And pf2.Cells(9, 6) <> "—" Then
        pf2.Cells(9, 7) = Round(pf2.Cells(9, 6) - pf2.Cells(9, 5), 2)
        pf2.Cells(9, 7).Value = pf2.Cells(9, 7).Value
    End If
    If pf2.Cells(8, 3) = "—" And pf2.Cells(10, 3) = "—" Then
        pf2.Cells(10, 5) = "—"
    ElseIf pf2.Cells(8, 3) = "—" And pf2.Cells(10, 3) <> "—" Then
        pf2.Cells(10, 5) = Round(pf2.Cells(10, 3) / 1, 2)
    ElseIf pf2.Cells(8, 3) <> "—" And pf2.Cells(10, 3) = "—" Then
        pf2.Cells(10, 5) = "—"
    Else
        pf2.Cells(10, 5) = Round(pf2.Cells(10, 3) / pf2.Cells(8, 3), 2)
    End If
    If pf2.Cells(8, 3) = "—" And pf2.Cells(10, 4) = "—" Then
        pf2.Cells(10, 6) = "—"
        ElseIf pf2.Cells(8, 3) = "—" And pf2.Cells(10, 4) <> "—" Then
            pf2.Cells(10, 6) = Round(pf2.Cells(10, 4) / 1, 2)
        ElseIf pf2.Cells(8, 3) <> "—" And pf2.Cells(10, 4) = "—" Then
            pf2.Cells(10, 6) = "—"
    Else
        pf2.Cells(10, 6) = Round(pf2.Cells(10, 4) / pf2.Cells(8, 3), 2)
    End If
    If pf2.Cells(10, 5) = "—" And pf2.Cells(10, 6) = "—" Then
        pf2.Cells(10, 7) = "—"
    ElseIf pf2.Cells(10, 5) = "—" And pf2.Cells(10, 6) <> "—" Then
        pf2.Cells(10, 7) = pf2.Cells(10, 6)
    ElseIf pf2.Cells(10, 5) <> "—" And pf2.Cells(10, 6) = "—" Then
        pf2.Cells(10, 7) = -pf2.Cells(10, 5)
    ElseIf pf2.Cells(10, 5) <> "—" And pf2.Cells(10, 6) <> "—" Then
        pf2.Cells(10, 7) = Round(pf2.Cells(10, 6) - pf2.Cells(10, 5), 2)
        pf2.Cells(10, 7).Value = pf2.Cells(10, 7).Value
    End If
    If pf2.Cells(8, 3) = "—" And pf2.Cells(11, 3) = "—" Then
        pf2.Cells(11, 5) = "—"
    ElseIf pf2.Cells(8, 3) = "—" And pf2.Cells(11, 3) <> "—" Then
        pf2.Cells(11, 5) = Round(pf2.Cells(11, 3) / 1, 2)
    ElseIf pf2.Cells(8, 3) <> "—" And pf2.Cells(11, 3) = "—" Then
        pf2.Cells(11, 5) = "—"
    Else
        pf2.Cells(11, 5) = Round(pf2.Cells(11, 3) / pf2.Cells(8, 3), 2)
    End If
    If pf2.Cells(8, 3) = "—" And pf2.Cells(11, 4) = "—" Then
        pf2.Cells(11, 6) = "—"
        ElseIf pf2.Cells(8, 3) = "—" And pf2.Cells(11, 4) <> "—" Then
            pf2.Cells(11, 6) = Round(pf2.Cells(11, 4) / 1, 2)
        ElseIf pf2.Cells(8, 3) <> "—" And pf2.Cells(11, 4) = "—" Then
            pf2.Cells(11, 6) = "—"
    Else
        pf2.Cells(11, 6) = Round(pf2.Cells(11, 4) / pf2.Cells(8, 3), 2)
    End If
    If pf2.Cells(11, 5) = "—" And pf2.Cells(11, 6) = "—" Then
        pf2.Cells(11, 7) = "—"
    ElseIf pf2.Cells(11, 5) = "—" And pf2.Cells(11, 6) <> "—" Then
        pf2.Cells(11, 7) = pf2.Cells(11, 6)
    ElseIf pf2.Cells(11, 5) <> "—" And pf2.Cells(11, 6) = "—" Then
        pf2.Cells(11, 7) = -pf2.Cells(11, 5)
    ElseIf pf2.Cells(11, 5) <> "—" And pf2.Cells(11, 6) <> "—" Then
        pf2.Cells(11, 7) = Round(pf2.Cells(11, 6) - pf2.Cells(11, 5), 2)
        pf2.Cells(11, 7).Value = pf2.Cells(11, 7).Value
    End If

`QSDU{RL(P[KB2ESV%9T.png
头像被屏蔽

TA的精华主题

TA的得分主题

发表于 2018-5-16 12:22 | 显示全部楼层
头像被屏蔽

TA的精华主题

TA的得分主题

发表于 2018-5-16 12:24 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2018-5-16 12:29 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2018-5-16 12:45 | 显示全部楼层
mikezhan 发表于 2018-5-16 12:24
上表!!!!!!!!!!! 把你的要求 写清楚

你好,每一行的代码都会涉及以下判断。总计70行
If pf2.Cells(8, 3) = "—" And pf2.Cells(9, 3) = "—" Then
        pf2.Cells(9, 5) = "—"
    ElseIf pf2.Cells(8, 3) = "—" And pf2.Cells(9, 3) <> "—" Then
        pf2.Cells(9, 5) = Round(pf2.Cells(9, 3) / 1, 2)
    ElseIf pf2.Cells(8, 3) <> "—" And pf2.Cells(9, 3) = "—" Then
        pf2.Cells(9, 5) = "—"
    Else
        pf2.Cells(9, 5) = Round(pf2.Cells(9, 3) / pf2.Cells(8, 3), 2)
    End If
    If pf2.Cells(8, 3) = "—" And pf2.Cells(9, 4) = "—" Then
        pf2.Cells(9, 6) = "—"
        ElseIf pf2.Cells(8, 3) = "—" And pf2.Cells(9, 4) <> "—" Then
            pf2.Cells(9, 6) = Round(pf2.Cells(9, 4) / 1, 2)
        ElseIf pf2.Cells(8, 3) <> "—" And pf2.Cells(9, 4) = "—" Then
            pf2.Cells(9, 6) = "—"
    Else
        pf2.Cells(9, 6) = Round(pf2.Cells(9, 4) / pf2.Cells(8, 3), 2)
    End If
    If pf2.Cells(9, 5) = "—" And pf2.Cells(9, 6) = "—" Then
        pf2.Cells(9, 7) = "—"
    ElseIf pf2.Cells(9, 5) = "—" And pf2.Cells(9, 6) <> "—" Then
        pf2.Cells(9, 7) = pf2.Cells(9, 6)
    ElseIf pf2.Cells(9, 5) <> "—" And pf2.Cells(9, 6) = "—" Then
        pf2.Cells(9, 7) = -pf2.Cells(9, 5)
    ElseIf pf2.Cells(9, 5) <> "—" And pf2.Cells(9, 6) <> "—" Then
        pf2.Cells(9, 7) = Round(pf2.Cells(9, 6) - pf2.Cells(9, 5), 2)
        pf2.Cells(9, 7).Value = pf2.Cells(9, 7).Value

表1.zip

29.85 KB, 下载次数: 7

TA的精华主题

TA的得分主题

发表于 2018-5-16 13:19 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2018-5-16 13:28 | 显示全部楼层
hmlstfqtl 发表于 2018-5-16 13:19
需求?要实现什么?

1、从第9行开始,判断执行
If pf2.Cells(8, 3) = "—" And pf2.Cells(9, 3) = "—" Then
        pf2.Cells(9, 5) = "—"
    ElseIf pf2.Cells(8, 3) = "—" And pf2.Cells(9, 3) <> "—" Then
        pf2.Cells(9, 5) = Round(pf2.Cells(9, 3) / 1, 2)
    ElseIf pf2.Cells(8, 3) <> "—" And pf2.Cells(9, 3) = "—" Then
        pf2.Cells(9, 5) = "—"
    Else
        pf2.Cells(9, 5) = Round(pf2.Cells(9, 3) / pf2.Cells(8, 3), 2)
    End If
    If pf2.Cells(8, 3) = "—" And pf2.Cells(9, 4) = "—" Then
        pf2.Cells(9, 6) = "—"
        ElseIf pf2.Cells(8, 3) = "—" And pf2.Cells(9, 4) <> "—" Then
            pf2.Cells(9, 6) = Round(pf2.Cells(9, 4) / 1, 2)
        ElseIf pf2.Cells(8, 3) <> "—" And pf2.Cells(9, 4) = "—" Then
            pf2.Cells(9, 6) = "—"
    Else
        pf2.Cells(9, 6) = Round(pf2.Cells(9, 4) / pf2.Cells(8, 3), 2)
    End If
    If pf2.Cells(9, 5) = "—" And pf2.Cells(9, 6) = "—" Then
        pf2.Cells(9, 7) = "—"
    ElseIf pf2.Cells(9, 5) = "—" And pf2.Cells(9, 6) <> "—" Then
        pf2.Cells(9, 7) = pf2.Cells(9, 6)
    ElseIf pf2.Cells(9, 5) <> "—" And pf2.Cells(9, 6) = "—" Then
        pf2.Cells(9, 7) = -pf2.Cells(9, 5)
    ElseIf pf2.Cells(9, 5) <> "—" And pf2.Cells(9, 6) <> "—" Then
        pf2.Cells(9, 7) = Round(pf2.Cells(9, 6) - pf2.Cells(9, 5), 2)
        pf2.Cells(9, 7).Value = pf2.Cells(9, 7).Value
这是第九行,5,6,7列的代码,还有70行,都需要做出判断,算出结果
过程太大,执行错误

TA的精华主题

TA的得分主题

 楼主| 发表于 2018-5-16 13:42 | 显示全部楼层
头像被屏蔽

TA的精华主题

TA的得分主题

发表于 2018-5-16 14:21 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2018-5-16 14:34 | 显示全部楼层
sub kb()      about for loop
Range("E8").resize(,3) = array("100%","100%","0%")
for i = 8 to 70
     if range("C" & i) <> "-" and  range("C" & i +1) <> "-" then
        range("K" & i +1) = "-"
     elseif range("C" & i) <> "-" and  range("C" & i +1) <> "-" then
        range("E" & i+1) = round(range("C",i+1)/1,2)
     end if
next i
end sub
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关闭

最新热点上一条 /1 下一条

关注官方微信,每天学会一个新技能

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2018-10-18 02:52 , Processed in 0.084880 second(s), 10 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 2001-2017 Wooffice Inc.

   

沪公网安备 31011702000001号 沪ICP备11019229号

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:徐怀玉律师 李志群律师

快速回复 返回顶部 返回列表