0

I'm analyzing some light spectra and am trying to select the intensity readings correspondent to certain wavelength, and paste them in a new worksheet. The code does run, however it presents a

Run-time error '1004' - Application-defined or object-defined error on the Worksheets(2).Cells(i, "B").Value = Worksheets(1).Cells(greenwl, columnnumber).Value code line

Sub GreenWlValues()

Dim targetwl As Long
Dim greenwl As Long
Dim columnnumber As Long
Dim i As Long 'used to denote the cell rows in the new Worksheet 

targetwl = 9 'example value
columnnumber = 2
i = 2

For greenwl = 1 To Cells(Rows.Count, 1).End(xlUp).Row

 Do While  Cells(greenwl, "A").Value = targetwl

  Worksheets(2).Cells(i, "B").Value = Worksheets(1).Cells(greenwl, 
  columnnumber).Value

  columnnumber = columnnumber + 1
  i = i + 1

  Loop

 Next greenwl


End Sub
Infante
  • 15
  • 2
  • 3
    1) There should be an explainig text like "method .... failed". What is the text? 2) When you debug the code in which line does the error come up? ( Worksheets(2).Cells .... ?) – marsh-wiggle Aug 12 '19 at 06:55
  • 3
    You never reset `columnnumber`. You are also probably looping in wrong bounds due to the [unqualified `Cells` calls](https://stackoverflow.com/q/17733541/11683). – GSerg Aug 12 '19 at 06:55

1 Answers1

0

You can't pass column name as alphabet in "cell" object so you have to change "B" to 2 . Below find the updated code :

Sub GreenWlValues()

Dim targetwl As Long
Dim greenwl As Long
Dim columnnumber As Long
Dim i As Long 'used to denote the cell rows in the new Worksheet

targetwl = 9 'example value
columnnumber = 2
i = 2

For greenwl = 1 To Cells(Rows.Count, 1).End(xlUp).Row

 Do While Cells(greenwl, 1).Value = targetwl

  Worksheets(2).Cells(i, 2).Value = Worksheets(1).Cells(greenwl,columnnumber).Value

  columnnumber = columnnumber + 1
  i = i + 1

  Loop

 Next greenwl


End Sub
yashika vaish
  • 201
  • 5
  • 19