請問VBA語法要怎麼寫以下這段判別式呢？

MaverickRay

我的試算表資料來源抓取後，分別有B、C、D欄
B欄 為使用金額
C欄 為尚存餘額
D欄 是我要用來做淨額判別的  (也就是說D欄是我自行用VBA插入)
但我不想直接將公式丟到EXCEL裡，而是由VBA試算 C-B 後之值  後再丟入D欄
問題來了.由於來源處的 D欄，在沒花費時不是用 0 或是空白，而是改用 --  來表示
如此若我將程序寫成如下：


For a = 2 To 1000   '共計1000列資料
      Cells(a, "D") = Cells(a, "C") - Cells(a, "B")
next


但一旦遇到 B欄裡之值為 -- 時，程式便會停下了..
想知道若我要用判別式來加以區分..
使其判別到  B欄 之值為 -- 時，便直接將 C欄之值 丟入 D欄 即可的條件式要如何寫呢？


我用了if 但語法不太通..一直出現編譯錯誤...
如下：
Cells(a, "D") = "=IF Cells(a, "B")="--", Cells(a, "C"),Cells(a, "C") - Cells(a, "B")


懇請高手幫幫忙解惑..感恩不盡~~謝謝註：後來我發現原來是我的IF判別式多加了個(，導致語法有誤，這問題目前被自己亂解解開了，不過還是有個小問題，由於下載的來源處不見得每次都會照規則將B、C欄都放在同一欄位的位置，有時會有多插入幾欄在前方..
Q1、若我想利用VBA去自動抓頂端列的特定文字後，針對這欄位來做公式名稱命名。公式名稱命名的方法我用錄製巨集時知道程序碼.但要如何做判別式來讓VBA自己去抓有特定文字的欄位，然後再套用到公式名稱去命名呢？
其次若Q1無法或大大們也不知道，我只好用半人工的方法來命名..
Q2、半人工命名後，我要怎麼樣將公式名稱直接套入到VBA裡呢?
以上段陳述式來說
For a = 2 To 1000   '共計1000列資料
      Cells(a, "D") = Cells(a, "C") - Cells(a, "B")
next

我若想改成
For a = 2 To 1000   '共計1000列資料
      Cells(a, "淨額欄") = Cells(a, "尚存餘額欄") - Cells(a, "使用金額欄")
next

陳述式裡的中文名稱都是在EXCEL裡自訂公式名，但我發現這樣的語法在VBA編譯時會出錯，想請教有大大可以幫忙我解惑嗎？祝各位心想事成、萬事如意了~~~感謝~~~

mick

什麼鬼？
一個小時內只能發文一次？

想回覆問題的熱情都沒有了。



方法一:
on error resume next
For a = 2 To 1000   '共計1000列資料
      Cells(a, "D") = Cells(a, "C") - Cells(a, "B")
next

方法二:
For a = 2 To 1000   '共計1000列資料
if IsNumeric(Cells(a, "B")) = true then
      Cells(a, "D") = Cells(a, "C") - Cells(a, "B")
end if
next

方法三:
For a = 2 To 1000   '共計1000列資料
if Cells(a, "B") <> "--" then
      Cells(a, "D") = Cells(a, "C") - Cells(a, "B")
end if

winso

如果是我 , 我不會去用字定義名稱

Sub test()

' 在 Sheet1 的 第一列 分別寫入 欄位名稱 ( 非字定義名稱 )
' 例如 D1 = 淨額欄 , C1 = 尚存餘額  , B1 = 使用金額

On Error Resume Next
淨額欄 = Cells.Find("淨額").Column
尚存餘額欄 = Cells.Find("尚存餘額").Column
使用金額欄 = Cells.Find("使用金額").Column
On Error GoTo 0

If 淨額欄 = 0 Then MsgBox "無法找到對應的淨額欄": Stop
If 尚存餘額欄 = 0 Then MsgBox "無法找到對應的尚存餘額欄": Stop
If 使用金額欄 = 0 Then MsgBox "無法找到對應的使用金額欄": Stop


a = 1

Cells(a, 淨額欄) = Val(Cells(a, 尚存餘額欄)) - Val(Cells(a, 使用金額欄))


End Sub

winso

(1)  
Columns("U:U").Select  這樣的程序  而奇怪的在我沒有動這個陳述式，但編譯執行時偶而會出現偵錯
==============
假設你要執行的  Columns("U:U").Select 是  Sheet1 (工作表1)
但這時 你的 EXCEL 畫面卻處於  Sheet2 (工作表2) 或  Sheet3 (工作表3) .... 就會出錯

因為要能成功執行 Columns("U:U").Select , 必須 EXCEL 畫面同時處於  Sheet1 (工作表1)
你必須改寫成

Sheet1.Select   ' --> 保證讓 EXCEL 畫面同時處於  Sheet1 (工作表1)
Columns("U:U").Select

(2)  
自訂義名稱後

Cells(i, [淨額欄].Column) = Val(Cells(a, [尚存餘額欄].Column)) - Val(Cells(a, [使用金額)欄].Column)

而且你的 a 值 還沒指明是哪一列

abab47036

不知有沒有試過
For a = 2 To 1000   '共計1000列資料
     Cells(a, "D").Select
     Cells(a, "D").FormulaR1C1 = _
        "=IF(RC[-2]=""--"",RC[-1],RC[-1]-RC[-2])"
Next

winso

       If Cells(1, i) = mykeyword1 Then

      ' Columns(i).Select  
       Columns(i).CreateNames Top:=True

        MsgBox "使用金額在第1列的第" & i & "欄!"

        Exit For
       End If

MaverickRay

abab47036 發表於 19-4-6 08:42
不知有沒有試過
For a = 2 To 1000   '共計1000列資料
     Cells(a, "D").Select

先謝謝你的回覆，且附上解決的方法..更是感謝
這問題我後來是用cells的方式+迴圈 與 判斷式if...then...elseif
來判斷解決的..
我仔細看了下，你的判斷式條件如下：
   "=IF(RC[-2]=""--"",RC[-1],RC[-1]-RC[-2])"
並沒有用到then跟elseif...
所以應該是我語法不夠熟悉還不能掌握關鍵要素所致.
但答案應該跟你附的用R1C1的方式的結果一樣..待會我把它複製過去試試..


但現在遇到的問題是，因為下載的來源檔，偶而會遇需求不同而在前方或不特定處插入新欄位，若我將這些程序用個人巨集存下，則日後一旦遇到欄位有被插入之現像時，將會造成抓錯資料的冏境..


所以我昨日想了下，或許我可以用判別式來抓標題列的特定文字，例如當判斷式抓到使用二字時，則將該欄位名稱定義成"使用金額"，以此類推...然後再利用VBA的方式，來讓Excel內的公式名稱直接進行相減之動作..
但目前我查了許久，就是找不到如何讓VBA能直接套用Excel公式名稱的方法..
舉例如同上文中的後段程序碼..
在Excel裡，若直接用公式名稱來做相減時，則例如:
B欄名稱自定為"使用金額"
C欄名稱自定為"尚存餘額"
D欄則自定為"淨額"
然後在D1儲存格內用公式，  =尚存餘額-使用金額
D2、D3、D4......Dn   則用填滿控點的方式，裡面的公式都全部為 =尚存餘額-使用金額
只是這種方式用久了，當累積資料變多，加上其它欄位的儲存格若有其它的判別公式時，會使得該試算表開啟很慢且佔用較大記憶體(個人的感覺)
所以百思下，覺得還是全丟給VBA後讓VBA丟回純文字會較妥，且較不易日後公式不小心更動造成圖表等錯亂..(畢竟很多自己弄的表格最後都曾因發生樞紐表、圖的問題後，重者全毀、輕者表、圖、公式名稱、格式等全刪)..


所以不知道大大們知不知道如何讓VBA去抓取試算表裡的公式名稱，且能夠用迴圈的方式來一一抓取...
或是有別的方法可解決當新插入欄位後，原公式能自動判別的解決之道，都在此感激不盡...謝謝

MaverickRay

mick 發表於 19-4-9 17:18
什麼鬼？
一個小時內只能發文一次？

你回覆的正是我那天晚上後來用的解決方法，雖然跟上面的大大，以R1C1的方式比較起來，cells籠長的許多...
後來我也改成上面大大的那段陳述式了，因為這樣日後管理起來應該會比較方便..
只是現在在找出如何改用EXCEL的內部自訂公式名稱的用法...
如果成功了，則看來要改成 range物件 或 cells物件 會更方便撰寫.
只是啊...這如同我在這篇文發問的..
http://www.coco-in.net/forum.php?mod=viewthread&tid=149760&page=1#pid858277
(你也有回答，可是答案跑出來不太懂是什麼結果)
這是您的回覆，先謝謝了

for j = 1 to 10      debug.print sheets("工作表1").cells(j,"A") next

因為我跟你一樣遇到這裡的困境。要一小時才給發文或回覆.....很麻煩...
所以就貼到這裡一塊提問與感謝
另外我把那帖的程序碼貼過來..若有高手能順道解惑，在此先說聲謝謝


Public Sub 抓特字所在的欄位()
  mykeyword1 = "使用金額"
  myKeyWord2 = "尚存餘額"
  Sheets(1).Select
  j = Cells(1, Columns.Count).End(xlToLeft).Column  '計算第一列中含資料的連續格共有幾欄
    For i = 1 To j
       If Cells(1, i) = mykeyword1 Then
       '   Range("i:i").Select    '這裡會變成只針對實際欄位  i  欄做整欄選取...但我想做的是讓 i 這變數能經由range來做整欄選取
       Selection.CreateNames Top:=True
       MsgBox "使用金額在第1列的第" & i & "欄!"
       Exit For
       End If
   Next
End Sub


===================================================
這段程式碼執行後，若第一列裡的儲存格有  "使用金額" 的文字 時，將會經由msgbox函數跳出且告之使用者這個(使用金額)儲存格的所在位置在第幾列、第幾欄..
但是，其實我是想將他所在的地方變成整欄選起後來讓程式自己去命名公式名稱，只是不知道方法，究竟要如何才能讓Range("")  乖乖聽話的把變數值轉成欄位呢？..
望高手幫忙了...感恩啊，我想了好幾天，也找了好久..但找到的都很複雜....

MaverickRay

..

MaverickRay

winso 發表於 19-4-10 00:08
If Cells(1, i) = mykeyword1 Then

      ' Columns(i).Select  

酷斃了~~感謝有你
不過我有點小疑惑想請教，同樣的columns物件.
先前我用錄製巨集來看圈選欄位時之程式碼，出來的也是以如  Columns("U:U").Select  這樣的程序
而奇怪的在我沒有動這個陳述式，但編譯執行時偶而會出現偵錯，就錯在columns這行裡
所以後來我才改成取 Range物件 來圈欄位..
想請教大大這是為什麼會出錯呢?
不過不論如何，你真的幫了我極大之忙，感謝有您..^^~~真是太好了...待會在依法炮製一個一個自動改名..


對了，那不知道大大知不知道有沒有方法可以讓我在將欄位自訂公式名稱後，然後再利用VBA來直接針對公式名稱做加減的方法呢..?
例如，這段程序========================
Public Sub 抓特字所在的欄位()
  mykeyword1 = "使用金額"
  myKeyWord2 = "尚存餘額"
  Sheets(1).Select
  j = Cells(1, Columns.Count).End(xlToLeft).Column
   For i = 1 To j
      If Cells(1, i) = mykeyword1 Then
      Columns(i).CreateNames Top:=True
      Cells(i, "淨額欄") = Cells(a, "尚存餘額欄") - Cells(a, "使用金額欄")


執行起來都會出現編譯錯誤..顯示型態不符合.
程式碼打的怪怪的，明日我弄清楚後再整個重貼...腦子有點昏昏的..
還是說要用別的方法才可以代入公式名稱.??
總之還是要再次說聲謝謝您..祝您心想事成..感謝~

MaverickRay

winso 發表於 19-4-10 08:34

(1)  
Columns("U:U").Select  這樣的程序  而奇怪的在我沒有動這個陳述式，但編譯執行時偶而會出現偵錯
== ...

本帖最後由 winso 於 19-4-10 08:35 編輯

Sheet1.Select   ' --> 保證讓 EXCEL 畫面同時處於  Sheet1 (工作表1)
Columns("U:U").Select


關於(1)這塊，我今天去查了下書時，印像中我好像看到的是Range物件，這物件好像也是當使用其方法為Select 與  Activate  時，若呼叫的工作表範圍未在使用中時，則陳述式碼便會偵錯在那一行
所以我就在想或許columns也是...這麼一說應該就是我雖然改為用Range物件，不過也只是剛好因為這工作簿的工作表被我啟用中，所以才未出現錯誤吧..
呵..你果然是高手..
另外可以用sheet1.select嗎?
我看書中講的好像是要用sheets(1).select..
等等我再試試看...說不定又學到新招..
================================
關於(2)  
自訂義名稱後

Cells(i, [淨額欄].Column) = Val(Cells(a, [尚存餘額欄].Column)) - Val(Cells(a, [使用金額)欄].Column)
而且你的 a 值 還沒指明是哪一列

後來我利用先前你教的方式，先用判別式  if ..then..elseif ....
一個個去比對要抓的字所在儲存格，然後針對該欄去設定了公式名稱..
途中發現 if ..then..elseif ...  若順序搞錯了，就會只抓到一欄後，訂了一欄的公式名稱後就跳出判斷式了..
後來我把它反過來做就成事了..

最後剛剛我用了迴圈的方法，去設定變數a ，然後參照您上方的
Cells(i, [淨額欄].Column) = Val(Cells(a, [尚存餘額欄].Column)) - Val(Cells(a, [使用金額)欄].Column)

將其修改成
  Cells(a, [淨額欄].Column) = Val(Cells(a, [尚存餘額欄].Column)) - Val(Cells(a, [使用金額欄].Column))
執行時程序出現錯誤顯示
編譯錯誤，引數不為選擇性(optional)

我偵錯結果是出在上頭陳述式裡
  Cells(a, [淨額欄.Column) = Val(Cells(a, [尚存餘額欄].Column)) - Val(Cells(a, [使用金額欄].Column))

淨額欄這裡....覺得很奇怪，切到 EXCEL的公式\名稱管理員   去看實際上是有利用程式定義到淨額欄的..且名稱內也沒有空格或底線等...但就是會出錯而不進行後續的加減..

倘若我把它修改為
  Cells(a, "D") = Val(Cells(a, [尚存餘額欄].Column)) - Val(Cells(a, [使用金額欄].Column))
則 D欄 裡便會一一的幫我做相減後之餘額..
可是這樣一來又違背了當初就是擔心欄位錯位的問題..
於是我又用了
sheets(1).select
然後才接Cells(a, [淨額欄.Column) = Val(Cells(a, [尚存餘額欄].Column)) - Val(Cells(a, [使用金額欄].Column))
結果問題一樣..出在 [淨額欄] 這個地方

之後我把它修改
Cells(a, Range("淨額欄").Column) = Val(Cells(a, [尚存餘額欄].Column)) - Val(Cells(a, [使用金額欄].Column))
執行就OK了..

可是這樣很奇怪，我查了書本，發現 range可以省略..就如同您的回覆一樣用 [ ] 即可...
可是我用 [ ] 卻會一直出錯，而後方的相減用 [ ] 卻又可以正常執行....= ="""

至於 val 這物件是什麼..怎麼這麼好用..
本來我還以為是大大你漏打前方的val，所以我加回去變成如下
val(Cells(a, [淨額欄.Column))= Val(Cells(a, [尚存餘額欄].Column)) - Val(Cells(a, [使用金額欄].Column))
可惜的還是出錯..哈哈..功夫不到家..真慚愧了..
我待會再來查查..還是要在說聲謝謝你..真幫了我一個很大且放了多年的大忙了~~
(怪，改了老半天，底色一直呈綠底...)= ="""

MaverickRay

winso 發表於 19-4-11 08:55
如果是我 , 我不會去用字定義名稱

Sub test()

奇怪原來評分處不能打太長，結果被截文了~~= ="
您這段我看不是很懂，晚點我再仔細研究再同您請教一二.
之所以會用公式名稱是因為先前有發現欄位會有插入等現像，以致錯位後抓錯資料誤判..
不過或許您後來的這段更能解決目前的困境..
畢竟我這幾天晚上搞好久..
在整理統合整段程式碼時發現利用公式名稱時，語法上的問題以及程序先後執行的邏輯性問題..
總之有夠灰煞煞地..但是還是多虧有大大的大力協助與分享..
解決了最初的根本..
感謝..我晚點再研究看看..試試那段程序碼的作用..^^..謝謝囉~~