0

First of all I would like to introduce myself. Iam Miguel and I recently started to learn VBA in order to improve and save time at work. I am familiar with formulas, all types, but when turning to VBA I get sometimes stuck.

I am trying to loop the range A2:A355 from Sheet"Aux" and copy each value to sheet "CS", and each value shall be pasted in Column A:A, but with the offset given in range B2:B355 Sheet "Aux". For Example I give the example attached. Sample Code:

This is the code:

Sub cablexsection() 
Dim s As Integer
Dim smax As Integer
smax = Sheets("Aux").Range("b1").Value

Sheets("CS").Activate

For s = 3 To smax
Sheets("CS").Cells(s, 1).Value = Sheets("Aux").Cells(s, 1).Value

'here I have to set the offset to down in order to paste cells given Sheets("Aux").Cells(s, 2) values

Next s

End Sub

And under the link you can find the file to be worked in:

Original File

Thank you very much and sorry if this question is repeated. I have tried to look through the forum but maybe I do not know what to write exactly.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
MiguelCM
  • 3
  • 1
  • Am I to understand what you are trying to do, for example, is take the value from sheet Aux of cell A3 and copy it to sheet CS relative to the position of the value from sheet Aux of cell A3 offsetting the rows by the value contained in the cell from sheet Aux of cell B3 (and so on...)? – sunsetsurf Apr 23 '18 at 20:50
  • Dear Scott, you are right. I Need to copy the values from Aux, each one pasted in CS Sheet offseted rows down according the value given in Aux. – MiguelCM Apr 24 '18 at 12:40

1 Answers1

0

Try this

Option Explicit

Sub CableXsection()
    Dim wsAux As Worksheet, wsCS As Worksheet
    Dim s As Long, sMax As Long, offSetCell As Range

    Set wsAux = ThisWorkbook.Worksheets("Aux")
    Set wsCS = ThisWorkbook.Worksheets("CS")

    sMax = wsAux.Range("B1").Value

    Application.ScreenUpdating = False

    For s = 3 To sMax

        Set offSetCell = wsAux.Cells(s, 2)    '2 is the offset column from the same row

        If Not IsError(offSetCell) And IsNumeric(offSetCell) Then
            wsCS.Cells(offSetCell.Value2 + s, 1).Value = wsAux.Cells(s, 1).Value
        End If
    Next

    Application.ScreenUpdating = True
End Sub
paul bica
  • 10,557
  • 4
  • 23
  • 42
  • Dear Paul, the code works, but not completely fine. Instead of start copying values 1,2, 3-Cond.A... starts copying from 3-Cond.C. I think you got with my problem but I get confused with the code: `wsCS.Cells(offSetCell.Value2 + s, 1).Value = wsAux.Cells(s, 1).Value` why is written´offSetCell.Value2´ instead ´offSetCell.Value+s, 1´? Thank you – MiguelCM Apr 24 '18 at 16:37
  • Are you sure it starts copying from value `3-Cond.A`? (the first 2 values are copied on row `3 + 26` and `3 + 22`) If you want to change the start row change the `3` from line `For s = 3 To sMax`. `.Value2` is faster to work with (see this [link - What is the difference between .text, .value, and .value2](https://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2/17363466#17363466)) – paul bica Apr 24 '18 at 17:09