How can I make it for my code to work in different columns of Excel?

Question

right now I am using a VBA Macro to dowload pictures from a file of my computer into my excel document.

It is thought to take the reference numbers from cells of Column A and insert the pictures in its respective cell in Column B.

I would like to achieve my Macro to work in more cells. So, that I also have reference numbers in Column C, E, G... and the pictures get inserted in Columns D, F, H... .

How should I change these codes so that they also work in other columns? Some loop, other formulas?


 Range("A1").Offset(1,0)
    Range("B1").Activate

      endRow = Range("A65536").End(xlUp).Row

      For rowLoop = 2 To endRow

         fileName = Trim(Cells(rowLoop, "A")) & ".png"

         If (Dir(folderName & fileName) <> "") Then

            ActiveSheet.Shapes.AddPicture folderName & fileName, msoFalse, msoTrue, Cells(rowLoop, "B").Left, Cells(rowLoop, "B").Top, _
                                          Cells(rowLoop, "B").Width, Cells(rowLoop, "B").Height



sThanks in advance for your answers!

Hi PEH. I tried to copy it in the text but when I wanted to post it, I get an error, saying that my post appears to contain code that is not properly formatted as code. It also tells me to indent all code by 4 spaces using the code by 4 but I don´t relaly know where is the problem in my code. — Andrés Parra Arcila, Mar 12 '20 at 10:28
And you must remove `On Error Resume Next`, this line of code hides **all** error messages, but the errors still occur, you just cannot see them! If you cannot see them you cannot fix the errors and if you don't fix them the code doesn't work correctly. Remove that line and fix your errors. — Pᴇʜ, Mar 12 '20 at 10:38
Well I added the On Error Resume Next because it happens oft that the pictures I need are not in the file and then I get lots of error messages. I — Andrés Parra Arcila, Mar 12 '20 at 10:42
Thanks for the link! But my problem is not that my code doesn't work. The one you see works perfectly for its purpose. My problem is that my coding skills are very limited and I don´t know how to make it, so that it also works in other cells in other columns simultaneously. — Andrés Parra Arcila, Mar 12 '20 at 10:54
Well first step would be stop using `.Select` (see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba)). Then you can use the `Range.Offset` method to offset rows and columns. Eg. `Range("A1").Offset(1, 0)` references to `A2`. So you can use that to get the other columns, eg using a loop. • The problem with your question is that it is a way too broad to give a concrete answer, so I'm just giving hints into the correct direction. — Pᴇʜ, Mar 12 '20 at 11:00
Something is not working. When I write Range("A1").Offset(1, 0) I get a Compile error: "Expected: =". What kind of loop should I use for this? Sorry I am not more concrete. It is quite difficult as a beginner to know what exactly is what I need to know. — Andrés Parra Arcila, Mar 12 '20 at 11:50
Ah, well you need to use it like you would use `Range("A1").Value = 5` writes 5 into A1 then `Range("A1").Offset(1, 0).Value = 5` writes 5 into A2 • Look below I gave an example that might be even easier to understand. — Pᴇʜ, Mar 12 '20 at 12:51

Pᴇʜ · Answer 1 · 2020-03-12T12:57:31.260

Here in an example to loop through your columns and jump always 2 columns

Option Explicit

Public Sub Example()
    Const LastRow As Long = 5 'just for the example

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    Dim iCol As Long
    For iCol = 1 To 8 Step 2 'loop through column 1 to 8 and jump 2 steps
        ws.Cells(1, iCol).Value = "read"       'column A, C, E, G
        ws.Cells(1, iCol + 1).Value = "write"   'column B, D, F, H

        'so here you would write your code that loops through the rows like
        Dim iRow As Long
        For iRow = 2 To LastRow
            ' and use iRow as row number
            ws.Cells(iRow, iCol).Value = "read" & iRow       'column A, C, E, G
            ws.Cells(iRow, iCol + 1).Value = "write" & iRow   'column B, D, F, H
      Next iRow
    Next iCol
End Sub

The result would be like below:

^{Image 1: Note it is animated and slowed down to show the process what happens in each step.}

Hope this helps you to find out how do do it with your code.

How can I make it for my code to work in different columns of Excel?

1 Answers1