VBA mid function med dynamisk referance

Kører du en form for for next løkke eller lignende? Altså hvordan får du vba til at indsætte formlen i flere forskellige kolonner?

Under alle omstændigheder bliver du nødt til at have en variabel fremfor 1+x.

Ja, jeg har en løkke. nedenfor har jeg klippet hele koden ind.

"Under alle omstændigheder bliver du nødt til at have en variabel fremfor 1+x"
x er en variable. Der må være en måde, hvor man kan skrive en variable ind i en formel, men mit forsøg virker ikke. ( det er x værdien den fejler på)

Sub CopyStoff()

Dim f As String
f = "1"

Dim i As Integer, e As Integer, x As Integer, y As Integer
i = 0 'col
e = 0 'row
x = 0 'start col
y = 0 'start row

i = 5 'col select E33
e = 33 'row
x = 0

Set sht = Worksheets("VbaTest")
For i = 5 To 20

    For e = 33 To 77

    ActiveSheet.Cells(e, i).Select


    Select Case True
    Case e = 33
    ActiveCell.FormulaR1C1 = "=MID(R[-28]C[2],1,1)"
    Case e = 34
    ActiveCell.FormulaR1C1 = "=MID(R[-29]C[0],2,1)"
    Case e = 35
    ActiveCell.FormulaR1C1 = "=MID(R[-30]C[x+1],3,3)"
    Case e = 36
    ActiveCell.FormulaR1C1 = "=MID(R[-31]C[x+1],6,1)"
    Case e = 37
    Case e = 38
    ActiveCell.FormulaR1C1 = "=MID(R[-33]C[x+1],7,1)"
    Case e = 39
    Case e = 40
    ActiveCell.FormulaR1C1 = "=MID(R[-35]C[x+1],8,1)"
    Case e = 41
    Case e = 42
    ActiveCell.FormulaR1C1 = "=HEX2DEC(CONCAT(R[-36]C[x],R[-35]C[x]))"
    Case e = 43
    ActiveCell.FormulaR1C1 = "=MID(R[-35]C[x+1],1,1)"
    Case e = 44
    ActiveCell.FormulaR1C1 = "=MID(R[-36]C[x+1],2,1)"
    Case e = 45
    ActiveCell.FormulaR1C1 = "=MID(R[-37]C[x+1],3,1)"
    Case e = 46
    ActiveCell.FormulaR1C1 = "=MID(R[-38]C[x+1],4,1)"
    Case e = 47
    ActiveCell.FormulaR1C1 = "=MID(R[-39]C[x+1],5,1)"
    Case e = 48
    ActiveCell.FormulaR1C1 = "=MID(R[-40]C[x+1],6,1)"
    Case e = 49
    ActiveCell.FormulaR1C1 = "=MID(R[-41]C[x+1],7,1)"
    Case e = 50
    ActiveCell.FormulaR1C1 = "=MID(R[-42]C[x+1],8,1)"
    Case e = 51
    ActiveCell.FormulaR1C1 = "=MID(R[-42]C[x+1],1,2)"
    Case e = 52
    Case e = 53
    Case e = 54
    Case e = 55
    ActiveCell.FormulaR1C1 = "=MID(R[-46]C[x+1],3,1)"
    Case e = 56
    ActiveCell.FormulaR1C1 = "=MID(R[-47]C[x+1],4,1)"
    Case e = 57
    ActiveCell.FormulaR1C1 = "=MID(R[-48]C[x+1],5,1)"
    Case e = 58
    ActiveCell.FormulaR1C1 = "=MID(R[-49]C[x+1],6,1)"
    Case e = 59
    ActiveCell.FormulaR1C1 = "=MID(R[-50]C[x+1],7,1)"
    Case e = 60
    ActiveCell.FormulaR1C1 = "=MID(R[-51]C[x+1],8,1)"
    Case e = 61
    ActiveCell.FormulaR1C1 = "=HEX2DEC(CONCAT(R[-51]C[x],R[-50]C[x],R[-49]C[x]))"
    Case e = 62
    ActiveCell.FormulaR1C1 = "=HEX2DEC(CONCAT(R[-49]C[x],R[-48]C[x]))"
    Case e = 63
    ActiveCell.FormulaR1C1 = "=HEX2DEC(CONCAT(R[-48]C[x],R[-47]C[x]))"
    Case e = 64
    ActiveCell.FormulaR1C1 = "=BIN2DEC(R[-47]C[x+1])"
    Case e = 65
    ActiveCell.FormulaR1C1 = "=MID(R[-47]C[x+1],1,4)"
    Case e = 66
    ActiveCell.FormulaR1C1 = "=MID(R[-48]C[x+1],5,4)"
    Case e = 67
    ActiveCell.FormulaR1C1 = "=MID(R[-48]C[x+1],1,4)"
    Case e = 68
    ActiveCell.FormulaR1C1 = "=MID(R[-48]C[x+1],5,4)"
    Case e = 69
    ActiveCell.FormulaR1C1 = "=R[-49]C[x+1]"
    Case e = 70
    ActiveCell.FormulaR1C1 = "=R[-49]C[x+1]"
    Case e = 71
    ActiveCell.FormulaR1C1 = "=R[-49]C[x+1]"
    Case e = 72
    ActiveCell.FormulaR1C1 = "=R[-49]C[x+1]"
    Case e = 73
    ActiveCell.FormulaR1C1 = "=R[-49]C[x+1]"
    Case e = 74
    ActiveCell.FormulaR1C1 = "=R[-49]C[x+1]"
    Case e = 75
    ActiveCell.FormulaR1C1 = "=HEX2DEC(R[-49]C[x])"
    Case e = 76
    ActiveCell.FormulaR1C1 = "=HEX2DEC(R[-49]C[x])"
    Case e = 77
    ActiveCell.FormulaR1C1 = "=HEX2DEC(R[-49]C[x])"
    End Select
   
    If e = 77 Then
    x = x + 1
    End If

    Next e
   

Next i
 

End Sub

Du kan ikke bruge en variabel indenfor anførselstegn. Du bliver nødt til at kombinere din line med & tegn.

F.eks.
ActiveCell.FormulaR1C1 = "=HEX2DEC(R[-49]C"&x