Adding two columns together in excel?

[unique12u]

Can you look at this section of VB that I have to see what is wrong. The rest of my script works fine without this. For every row of an excel sheet I need to set Column E equal to Column C + D


x = 1

DO Until xlSheet.Cells(x,1).Value = ""
If (ISBLANK(xlSheet.Cells(x,1).Value)) Then
xlSheet.Cells(x,5).Value = ""
Else
xlSheet.Cells(x,5).Value = (xlSheet.Cells(x,3).Value + xlSheet.Cells(x,4).Value)
Endif

x = x + 1

Loop

Thanks in Advance
Nique

[Marcus Tettmar]

Hi,

That bit of VBScript looks ok, though it's out of context and you don't say what error or problem you are having. Is this pure VBScript, or within a Macro Scheduler script? How are you calling this section of code? Maybe you could post the full script that shows it in context and an explanation of the error you are getting.

[unique12u]

Here is the full script so far. Still a work in progress. I need to tak a date in column C and a time in column E and combine them into one column in yyyy-mm-dd hh:mm:ss format. The bold code is my attempt at that so far. I am not getting anywhere fast. I want to take column C + Column D, put it in Column E and then delete column C and D, then reformat column E in correct format.

Edited to Add
I am getting a "Type Mismatch" error on the time column when it tries to add the two columns.



//Specify the path of the file here
Let>filename=c:\PriceNetInterface\Rack\Rack Price Query.dqy

ExecuteFile>filename

WaitWindowOpen>Microsoft Excel -*
Wait>10
Send Character/Text>O
Wait>20

Let>xlfile=c:\PriceNetInterface\Rack\Rack.xls

//Send ALT-fa to save
Press ALT
Send>fa
Release ALT
WaitWindowOpen>Save As

IfFileExists>xlfile,fileexists
Goto>savefile

Label>fileexists
DeleteFile>xlfile

Label>savefile
SetFocus>Save As
Send>xlfile
Press Enter

Press ALT
Send>fx
Release ALT
Send Character/Text>n
Wait>15
VBSTART
Sub Changefile()
Dim xlApp
Dim xlBook
Dim xlSheet

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.open("c:\PriceNetInterface\Rack\Rack.xls")
Set xlSheet = xlBook.Worksheets("Rack Price Query")
xlApp.visible = True

'xlSheet.Rows("1:1").Select
'xlSheet.Selection.delete xlShiftUp
xlSheet.Rows("1:1").delete 1
xlSheet.Columns("C:C").NumberFormat = "yyyy-mm-dd"
xlSheet.Columns("D:D").NumberFormat = "hh:mm:ss"
x = 1

Do Until xlSheet.Cells(x,1).Value = ""
If (xlSheet.Cells(x,6).Value)=00 Then
xlSheet.Cells(x,7).Value =(xlSheet.Cells(x,7).Value + ((xlSheet.Cells(x,7).Value+.184)*.06)+.384)
ElseIf (xlSheet.Cells(x,6).Value)=01 Then
xlSheet.Cells(x,7).Value =(xlSheet.Cells(x,7).Value + ((xlSheet.Cells(x,7).Value+.184)*.06)+.384)
ElseIf (xlSheet.Cells(x,6).Value)=02 Then
xlSheet.Cells(x,7).Value =(xlSheet.Cells(x,7).Value + ((xlSheet.Cells(x,7).Value+.184)*.06)+.384)
ElseIf (xlSheet.Cells(x,6).Value)=03 Then
xlSheet.Cells(x,7).Value =(xlSheet.Cells(x,7).Value + ((xlSheet.Cells(x,7).Value+.184)*.06)+.384)
ElseIf (xlSheet.Cells(x,6).Value)=04 Then
xlSheet.Cells(x,7).Value =(xlSheet.Cells(x,7).Value + ((xlSheet.Cells(x,7).Value+.244)*.06)+.404)
ElseIf (xlSheet.Cells(x,6).Value)=5 Then
xlSheet.Cells(x,7).Value =(xlSheet.Cells(x,7).Value + ((xlSheet.Cells(x,7).Value+.244)*.06)+.404)
ElseIf (xlSheet.Cells(x,6).Value)=141 Then
xlSheet.Cells(x,7).Value =(xlSheet.Cells(x,7).Value + ((xlSheet.Cells(x,7).Value+.133)*.06)+.333)
ElseIf (xlSheet.Cells(x,6).Value)=101 Then
xlSheet.Cells(x,7).Value =(xlSheet.Cells(x,7).Value + ((xlSheet.Cells(x,7).Value+.133)*.06)+.333)
ElseIf (xlSheet.Cells(x,6).Value)=102 Then
xlSheet.Cells(x,7).Value =(xlSheet.Cells(x,7).Value + ((xlSheet.Cells(x,7).Value+.133)*.06)+.333)
Else
xlSheet.Cells(x,7).Value = xlSheet.Cells(x,7).Value
End If

x = x + 1

Loop

x = 1

DO Until xlSheet.Cells(x,1).Value = ""
If (xlSheet.Cells(x,6).Value)=11 Then
xlSheet.Cells(x,6).Value = 3
Else
xlSheet.Cells(x,6).Value = xlSheet.Cells(x,6).Value
End If

x = x + 1

Loop

x = 1

DO Until xlSheet.Cells(x,1).Value = ""
If (xlSheet.Cells(x,1).Value) = "" Then
xlSheet.Cells.Value(x,5)= ""
Else
xlSheet.Cells(x,5).Value = xlSheet.Cells(x,3).Value + xlSheet.Cells(x,4).Value
End If

x = x + 1

Loop
End Sub
VBEND

//THIS LINE OPENS XL AND DELETES THE ROW
VBRun>Changefile


Let>csvfile=c:\PriceNetInterface\CSVCompetitorPriceImport\rack.csv
SetFocus>Microsoft Excel - *

//Send ALT-fa to save
Press ALT
Send>fa
Release ALT
WaitWindowOpen>Save As

IfFileExists>csvfile,csvfileexists
Goto>csvsavefile

Label>csvfileexists
DeleteFile>csvfile

Label>csvsavefile
SetFocus>Save As
Wait>2
Send Character/Text>c:\PriceNetInterface\CSVCompetitorPriceImport\rack
Wait>2
Press Tab
Send Character/Text>C
Press Enter
Press Alt
Send Character/Text>S
Release ALT
Press Enter
Send Character/Text>Y


Press ALT
Send>fx
Release ALT
Send Character/Text>N

[unique12u]

I got it to work. Not sure BEST solution, but it works

x = 1

DO Until xlSheet.Cells(x,1).Value = ""
If (xlSheet.Cells(x,1).Value) = "" Then
xlSheet.Cells.Value(x,5)= ""
Else
xlSheet.Cells(x,5).Value = ((xlSheet.Cells(x,3).Value) & " " & (xlSheet.Cells(x,4).Value))
End If

x = x + 1

Loop

xlSheet.Columns("E:E").NumberFormat = "yyyy-mm-dd hh:mm:ss"
'xlSheet.Columns("C:D").Select
'xlSheet.Selection.delete xlLeft
xlSheet.Columns("C:D").delete 2