CODEWITHSUNDEEP
excelvba
Michael Holder
Michael Holder

Reputation: 25

Writing a macro for a spreadsheet in excel

I need to write a spreadsheet which when you press a button adds a row of data and asks for the parameters needed for the calculations, but I cant seem to get it right, its really frustrating me, any help would be appreciated. I am a complete begginner to macros in excel and have only done very basic programming for matlab on my uni course. My script so far is as follows:

Sub AddPosTol()
'
' AddPosTol Macro
'
   Dim rngSeek As Range

   Set rngSeek = Range("A1").End(xlDown).Offset(1, 0)

   With rngSeek.Offset(0, 1)
       With .Font
           .Name = "Solid Edge ANSI1 Symbols"
           .Size = 11
       End With
  End With
  Range(rngSeek).Offset(0, 1) = "l"
  Range(rngSeek).Offset(0, 3) = "=RC[-1]"
  Range(rngSeek).Offset(0, 4) = "0"

  With rngSeek.Offset(1, 1)
      With .Font
        .Bold = True
      End With
  End With

  Range(rngSeek).Offset(1, 1) = "X value"
  Range(rngSeek).Offset(2, 1) = "Y Value"

  Range(rngSeek).Offset(0, 4) = "=2*SQRT((R[1]C[-3]-R[1]C)^2+(R[2]C[-3]-R[2]C)^2)"
  Range(rngSeek).Offset(0, 5) = "=2*SQRT((R4C3-R[1]C)^2+(R5C3-R[2]C)^2)"
  Range(rngSeek).Offset(0, 6) = "=2*SQRT((R[1]C[-3]-R[1]C)^2+(R[2]C[-3]-R[2]C)^2)"
  Range(rngSeek).Offset(0, 7) = "=2*SQRT((R[1]C[-3]-R[1]C)^2+(R[2]C[-3]-R[2]C)^2)"
  Range(rngSee).Offset(0, 8) = "=2*SQRT((R4C3-R[1]C)^2+(R5C3-R[2]C)^2)"

  Range(rngSeek).Offset(0, 2) = (InputBox("Insert Positional Tolerance Diametre"))
  Range(rngSeek).Offset(1, 2) = (InputBox("Insert X value on drawing"))
  Range(rngSeek).Offset(2, 2) = (InputBox("Insert Y value on drawing"))

End Sub

Upvotes: 1

Views: 324

Answers (1)

CuberChase
CuberChase

Reputation: 4518

You've defined rngSeek as a range and then are trying to use that range definition with the Range() method of the worksheet.

All the lines where you have Range(rngSeek).Offset(... you can replace with rngSeek.Offset(...

(One of your formula also references the wrong cells ;-)

Upvotes: 1

Related Questions