VBA HELP! - Need slight change to my code

[orbatrin]

Hello all, I created a simple form in a terminal mainframe at work that opens an excel file, grabs an item from excel in Column 1 Row 1 and puts it into my terminal system then runs the other code. I have added additional code to screen scrape my terminal system and stores value in a string variable. How do I add that item to the column 2 in the same excel file corresponding to the Column 1 item while in the same loop? Please see code below.

[VBA]Sub BKStatusCheck()
Dim MyExcelPath As String, GetExcelData As String
Dim MyExcel As excel.Workbook
Dim MyExcelRange As excel.Range
Dim BKStatus As String
' opens dialog box that was created to open the excel file to run
OpenFileDialog.Show
If OpenFileDialog.OKButton.Enabled Then
GoTo 100
OpenFileDialog.Hide
Else
If OpenFileDialog.CancelButton.Enabled Then
Exit Sub
End If
End If

100: MyExcelPath = OpenFileDialog.SelectedPath.Text
If MyExcelPath = "" Then
Exit Sub
End If

Set MyExcel = GetObject(MyExcelPath)
' loop to get each item in my excel sheet and place it back into a string variable
For Each MyExcelRange In MyExcel.Sheets("Sheet1").Columns(1).Rows(1).SpecialCells(xlCellTypeConstant s)
GetExcelData = MyExcelRange.Value

' below code is utilized by the Terminal system i'm using
With Session

.TransmitTerminalKey rcIBMClearKey
.TransmitTerminalKey rcIBMClearKey
.TransmitTerminalKey rcIBMClearKey
.TransmitANSI "BNK1" + GetExcelData
.TransmitTerminalKey rcIBMEnterKey
.Wait 1

' screen scrape my terminal system and place value in my string variable BKStatus
' I need to add the BKStatus item to the current row but in column 2 (column B) ********
BKStatus = .GetDisplayText(6, 4, 1)
End With

' continue to the next item in my excel sheet (ex. row 2, row3, row4)
Next
End Sub
[/VBA]

[p45cal]

grabs an item from excel in Column 1 Row 1
[vba]For Each MyExcelRange In MyExcel.Sheets("Sheet1").Columns(1).Rows(1).SpecialCells(xlCellTypeConstant s)
[/vba]

You realise that Column 1 Row 1 is a single cell (A1), and applying .SpecialCells(xlCellTypeConstants) to a single cell includes all cells on the whole sheet (all columns, all rows) which contain constants (not formulae). Had you chosen more than one cell to apply the .SpecialCells to it would only return cells with constants from amongst those cells.

If you only want to use cells containing constants from column 1, then lose the .Rows(1) from that line of code above.

Now to answer your question:
If MyExcelRange is in column 1, then
MyExcelRange.offset(,1).value = BKStatus
or
MyExcelRange.offset(,1).value = .getdisplaytext(6,4,1)

will place the values in column 2 (column B); The offset places the value in the cell to the right of the constant-containing cell.

If the constant cell can be anywhere on the excel sheet, but you want the value in column 2 regardless, then:
MyExcel.Sheets("Sheet1").cells(MyExcelRange.row,2).value = BKStatus

[orbatrin]

You realise that Column 1 Row 1 is a single cell (A1), and applying .SpecialCells(xlCellTypeConstants) to a single cell includes all cells on the whole sheet (all columns, all rows) which contain constants (not formulae). Had you chosen more than one cell to apply the .SpecialCells to it would only return cells with constants from amongst those cells.

If you only want to use cells containing constants from column 1, then lose the .Rows(1) from that line of code above.

Now to answer your question:
If MyExcelRange is in column 1, then
MyExcelRange.offset(,1).value = BKStatus
or
MyExcelRange.offset(,1).value = .getdisplaytext(6,4,1)

will place the values in column 2 (column B); The offset places the value in the cell to the right of the constant-containing cell.

If the constant cell can be anywhere on the excel sheet, but you want the value in column 2 regardless, then:
MyExcel.Sheets("Sheet1").cells(MyExcelRange.row,2).value = BKStatus

Thank you so much for your reply. I do know that Column 1 Cell 1 is a single cell A1 but did not know that .SpecialCells(xlCellTypeConstants) would look for every constant on the sheet. I figured it would only look in Column 1 range but it makes sense the way you explained it.

Thank you again for clarifying the offset. I will give it a try and let you know if I come across any issues.

Question: If I wanted to grab A1 data and also the B1 data, run my program and output to C1 field through my loop, would this for example work:

[VBA]For Each MyExcelRange In MyExcel.Sheets("Sheet1").Columns(1).SpecialCells(xlCellTypeConstants)
GetExcelData = MyExcelRange.Value
GetExcelData2 = MyExcelRange.offset(,1).Value
MyExcelRange.offset(,2).Value = BKStatus[/VBA]

[p45cal]

Question: If I wanted to grab A1 data and also the B1 data, run my program and output to C1 field through my loop, would this for example work:

[VBA]For Each MyExcelRange In MyExcel.Sheets("Sheet1").Columns(1).SpecialCells(xlCellTypeConstants)
GetExcelData = MyExcelRange.Value
GetExcelData2 = MyExcelRange.offset(,1).Value
MyExcelRange.offset(,2).Value = BKStatus[/VBA]

Yes.

[snb]

Please do not quote when answering a post !

But instead of using
[vba]
For Each MyExcelRange In MyExcel.Sheets("Sheet1").Columns(1).SpecialCells(xlCellTypeConstants)
GetExcelData = MyExcelRange.Value
GetExcelData2 = MyExcelRange.offset(,1).Value
next
[/vba]

you'd better write
[vba]
with MyExcel.Sheets("Sheet1").Columns(1).SpecialCells(xlCellTypeConstants)
.offset(,2)=.value
end with
[/vba]

or even:

[vba]MyExcel.Sheets("Sheet1").columns(3)=MyExcel.Sheets("Sheet1").columns(1).val ue[/vba]

[p45cal]

Please do not quote when answering a post !

I presume that's not directed at me. I just quote the parts that I'm addressing in my response. Oops, I've quoted again.

You do realise, snb, that the OP is grabbing 1 or 2 values from the sheet, using them on another system, then getting a value from that system and placing it on the sheet in another column. As far as I can see, your code copies values from column to column of the same sheet.

Besides, if there are non-contiguous cells (which is the whole point of SpecialCells) in column 1, they won't all be copied over properly to column 3 with your suggestion:[VBA]With MyExcel.Sheets("Sheet1").Columns(1).SpecialCells(xlCellTypeConstants)
.Offset(, 2) = .Value
End With
[/VBA]viz. Column C after running that snippet
2012-09-26_140653.jpg

[snb]

@p45cal

Let's hope no gaps in the column...

[VBA]
For Each it In MyExcel.Sheets("Sheet1").Columns(1).Rows(1).SpecialCells(2)
With Session
.TransmitTerminalKey rcIBMClearKey
.TransmitTerminalKey rcIBMClearKey
.TransmitTerminalKey rcIBMClearKey
.TransmitANSI "BNK1" + it.value
.TransmitTerminalKey rcIBMEnterKey
.Wait 1
it.offset(,1)=.GetDisplayText(6, 4, 1)
End With
Next
[/VBA]

[p45cal]

Hopefully it won't matter whether there are any gaps in Column 1, however, your latest suggestion (Columns(1).Rows(1)) is a single cell and the SpecialCells will return all constants on the sheet, any column, any row. The OP implies in msg#3 that he's only interested in Column 1 so you need to lose the .Rows(1).

Except for changing MyExcelRange to it, your suggestion is exactly the same as my suggestion in msg#2

[orbatrin]

Thank you for all your help. I modified my script accordingly and it works perfectly with a few hickups when running several thousand rows in column 1.

Below is the complete code that I have been running today: Sometimes the code breaks at and I'm pretty sure it's because of the lag in my Terminal system i'm using.

I removed the .Rows(1) from my line of code so it doesnt look for anything on the sheet besides Columns(1).

[vba]Sub BKStatusCheck()
Dim MyExcelPath As String, GetExcelData As String
Dim MyExcel As excel.Workbook
Dim MyExcelRange As excel.Range
Dim BKStatus As String, BKCase As String, OutputBKCase As String, GetBKCase As String
OpenFileDialog.Show
If OpenFileDialog.OKButton.Enabled Then
GoTo 100
OpenFileDialog.Hide
Else
If OpenFileDialog.CancelButton.Enabled Then
Exit Sub
End If
End If

100: MyExcelPath = OpenFileDialog.SelectedPath.Text
If MyExcelPath = "" Then
Exit Sub
End If

Set MyExcel = GetObject(MyExcelPath)
For Each MyExcelRange In MyExcel.Sheets("Sheet1").Columns(1).SpecialCells(xlCellTypeConstants)
GetExcelData = MyExcelRange.Value
GetBKCase = MyExcelRange.Offset(, 1).Value

With Session

.TransmitTerminalKey rcIBMClearKey
.TransmitTerminalKey rcIBMClearKey
.TransmitTerminalKey rcIBMClearKey
.TransmitANSI "BNK1" + GetExcelData
.TransmitTerminalKey rcIBMEnterKey
.Wait 1

BKStatus = .GetDisplayText(6, 4, 1)
BKCase = .GetDisplayText(6, 35, 8)

If BKCase = GetBKCase Then
OutputBKCase = "Case Match"
Else
OutputBKCase = "No Match"
End If
' offset the column by 1, moves column number to the right
MyExcelRange.Offset(, 3).Value = BKStatus 'This is where my code breaks sometimes.
MyExcelRange.Offset(, 4).Value = OutputBKCase

End With

Next
End Sub[/vba]

[snb]

[VBA]
For Each it In MyExcel.Sheets("Sheet1").Columns(1).SpecialCells(2)
With Session
.TransmitTerminalKey rcIBMClearKey
.TransmitTerminalKey rcIBMClearKey
.TransmitTerminalKey rcIBMClearKey
.TransmitANSI "BNK1" + it.value
.TransmitTerminalKey rcIBMEnterKey

do
doevents
c01=.GetDisplayText(6, 4, 1)
loop until not isempty(c01)

it.offset(,1)=.GetDisplayText(6, 4, 1)
End With
Next
[/VBA]