Use each row of spreadsheet column as variable in loop?

I have a column of spreadsheet data which represents prices. How can I use the data from cell A1 the first time through a loop, A2 the next, and so on 100 times or more?

I don't need a live link to the sheet, I could just copy it to the clipboard, so I guess the question is also "Can I iterate through clipboard lines during a loop?".

What is the output that you are defining or constructing ?

  • A new list / column ?
  • A sequence of events ?
  • Something else ?

And what's the spreadsheet ?

  • MS Excel ?
  • Apple Numbers ?
  • Google Sheets ?

Hi, thanks!

Spreadsheet is GDocs, but it doesn't really matter, as I said, in retrospect it might be that it's easier easier that I copy the column to clipboard first. There's no requirement for the data to be 'captured' from the spreadsheet, that's just where I refer to it, I can easily just copy it to clipboard for the purpose of the macro.

So on every loop I want to use the next "price" in a macro. Actually, it will be in a web page, filling in the prices of goods for an etsy produce. But that doesn't matter, I can fgure out all that part, the bit I don't understand is using a clipboard buffer that looks something like this

1.93
2.03
2.07
2.10

etc. for 100 prices/lines/rows

how to use each one in turn, in a loop. First time through loop use the top one, each consecutive time through the loop using the next 'price' in the clipboard list/column.

I'm pretty sure you can parse the clipboard line for line as you suggest. I don't know GDocs and haven't used KM to parse text data much, but in Excel I would loop through the following steps: Copy, use the price, down arrow. (to go to next cell). Until the clipboard is empty, assuming the first empty cell is the end of the data.

1 Like

4 Likes

Hi Richard, I'm trying to avoid iterating through the spreadsheet as that would have me having to go to another website between each loop and then back again, hence why I also said it was probably better I copied the spreadsheet data to clipboard. I must add, I tried what you suggested already but could see it was a little too much for my specific usage.

Hey CP, this is perfect for what I need. A very concise demo! Thanks so much.