CODEWITHSUNDEEP
windowsexcelscriptingvbscript
rugbert
rugbert

Reputation: 12673

Searching all cells in a column Excel with vbscript

I wrote a vbScript to gather computer information for each user's computer on login, and Id like to have the script record the information to an Excel sheet on the server, with each computer having its own row.

I wrote this like 6 years ago but lost the script and havent touched vbScript since.

So what I need to do is,

  1. check all the cells in column B (which would be computer name) that have a value
  2. compare that value to value saved for the computer's name
  3. if it matches, write the computer info to that row
  4. if there are no matches, then write the info to the first empty row

I have no idea where to start since vbScript is pretty foreign to me.

Edit - I have this loop so far and that echo to test it, but it only goes to 1, while I have like 6 rows with values in column 0. I tried that conditional to check the cell value for a value I know exists and I get a runtime error.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("test.xlsx")
Do While objExcel.ActiveCell.Offset(iRow,0).Value<>""
   WScript.Echo iRow
   If objExcel.Cells(iRow,0).Value = "three" Then
    WScript.Echo "three"
   End If
   iRow = iRow+1
Loop
WScript.Quit

Upvotes: 0

Views: 17704

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200503

You most likely have an On Error Resume Next somewhere in your script, which makes the code fail silently.

As far as I can tell from your code this is probably what's happening:

  • Do While objExcel.ActiveCell.Offset(iRow,0).Value<>""

    The script checks if the cell at the offset (iRow,0) of the active cell is empty and if it isn't goes into the loop.

  • WScript.Echo iRow

    The script echoes the current value of iRow (probably 0).

  • If objExcel.Cells(iRow,0).Value = "three" Then

    The script tries to access the cell (iRow,0) (not the cell at the offset (iRow,0)), which fails, because Cells() is not zero-based. The first row as well as the first column have the index 1 in the Cells() property. Not to mention that with Cells() you have to use the absolute row and column numbers, not the ones relative to ActiveCell as in Offset().

To fix the issue I'd remove On Error Resume Next and simply use Cells() instead of ActiveCell.Offset():

iRow = 1
Do While objExcel.Cells(iRow, 2).Value <> ""
  WScript.Echo iRow
  If objExcel.Cells(iRow, 2).Value = "three" Then
    WScript.Echo "three"
  End If
  iRow = iRow + 1
Loop

Upvotes: 2

Related Questions