VBA编辑错误，过程太大

qrfljh5131024 · 发表于 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

mikezhan · 发表于 2018-5-16 12:22

提示: 作者被禁止或删除内容自动屏蔽

mikezhan · 发表于 2018-5-16 12:24

提示: 作者被禁止或删除内容自动屏蔽

gdw831001 · 发表于 2018-5-16 12:29

不馈是做财务的，思路就是比一般人好使。

qrfljh5131024 · 发表于 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

hmlstfqtl · 发表于 2018-5-16 13:19

需求？要实现什么？

qrfljh5131024 · 发表于 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行，都需要做出判断，算出结果
过程太大，执行错误

qrfljh5131024 · 发表于 2018-5-16 13:42

高人，大师都哪去了？

mikezhan · 发表于 2018-5-16 14:21

提示: 作者被禁止或删除内容自动屏蔽

6688 · 发表于 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

		自动登录	找回密码
密码			免费注册

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

最新热点 /1

mikezhan mikezhan 当前离线性别保密 UID 736941 阅读权限 0 注册时间 2009-3-7 在线时间小时最后登录 1970-1-1 积分 975 财富分享技术精华头像被屏蔽 TA的精华主题 TA的得分主题	发表于 2018-5-16 12:22 \| 显示全部楼层 [广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★使用手册★ 提示: 作者被禁止或删除内容自动屏蔽

	回复举报

mikezhan mikezhan 当前离线性别保密 UID 736941 阅读权限 0 注册时间 2009-3-7 在线时间小时最后登录 1970-1-1 积分 975 财富分享技术精华头像被屏蔽 TA的精华主题 TA的得分主题	发表于 2018-5-16 12:24 \| 显示全部楼层 [广告] ★ExcelHome默写助手 - 不用妈妈陪的默写神器 · Word/WPS插件 · 自定义默写单 · 自定义描红字帖★ 提示: 作者被禁止或删除内容自动屏蔽

	回复举报

mikezhan mikezhan 当前离线性别保密 UID 736941 阅读权限 0 注册时间 2009-3-7 在线时间小时最后登录 1970-1-1 积分 975 财富分享技术精华头像被屏蔽 TA的精华主题 TA的得分主题	发表于 2018-5-16 14:21 \| 显示全部楼层 [广告] ★ ChatOffice，能听会写的AI办公，包含百种Excel、Word、PPT工具，兼容WPS，百倍效率！★ 提示: 作者被禁止或删除内容自动屏蔽

	回复举报