hunna
12-09-2011, 04:04 PM
Hi there, I have a problem at Cells(i, 1).Value. The error is "Method or data member not found"
any help would be appreciated,
Sub ds()
Dim i As Integer
Dim intRowCount As Long
Dim strText As String
Dim aryCharacters As Variant
Dim n As Long
With Sheets("Automate")
intRowCount = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To intRowCount
If Not Cells(i, 1).Value = "" Then
Cells(i, 1).Value = Results.Sheet1.Cells(i, 1).Value 'peptide A
Cells(i, 2).Value = Results.Sheet1.Cells(i, 2).Value 'K position
Cells(i, 3).Value = Results.Sheet1.Cells(i, 3).Value 'peptide B
Cells(i, 4).Value = Results.Sheet1.Cells(i, 4).Value 'K position
'Add peptide A
strText = Replace(Me.Cells(i, 1).Value, Chr(32), vbNullString)
If Len(strText) > 1 Then
If Len(strText) < ThisWorkbook.Worksheets(1).Columns.Count Then
'Optional, clear the row first:
Rows(3).ClearContents
ReDim aryCharacters(1 To 1, 1 To Len(strText))
For n = 1 To Len(strText)
aryCharacters(1, n) = Mid(strText, n, 1)
Next
Select Case Cells(i, 2).Value
Case 1
Set rngDst = Range("AE3")
Case 2
Set rngDst = Range("AD3")
Case 3
Set rngDst = Range("AC3")
Case 4
Set rngDst = Range("AB3")
Case 5
Set rngDst = Range("AA3")
Case 6
Set rngDst = Range("Z3")
Case 7
Set rngDst = Range("Y3")
Case 8
Set rngDst = Range("X3")
Case 9
Set rngDst = Range("W3")
Case 10
Set rngDst = Range("V3")
Case 11
Set rngDst = Range("U3")
Case 12
Set rngDst = Range("T3")
Case 13
Set rngDst = Range("S3")
Case 14
Set rngDst = Range("R3")
Case 15
Set rngDst = Range("Q3")
Case 16
Set rngDst = Range("P3")
Case 17
Set rngDst = Range("O3")
Case 18
Set rngDst = Range("N3")
Case 19
Set rngDst = Range("M3")
Case 20
Set rngDst = Range("L3")
Case 21
Set rngDst = Range("K3")
Case 22
Set rngDst = Range("J3")
Case 23
Set rngDst = Range("I3")
Case 24
Set rngDst = Range("H3")
Case 25
Set rngDst = Range("G3")
Case 26
Set rngDst = Range("F3")
Case 27
Set rngDst = Range("E3")
Case 28
Set rngDst = Range("D3")
Case 29
Set rngDst = Range("C3")
Case 30
Set rngDst = Range("B3")
End Select
rngDst.Resize(, UBound(aryCharacters, 2)).Value = aryCharacters
Else
MsgBox "Too long...", 0, vbNullString
End If
Else
MsgBox "Peptide sequence (A) and Crosslink position are required", _
vbOKOnly + vbCritical, "Warning"
End If
Cells(42, 33).Value = Cells(i, 5).Value 'precursor
Call Show_MGF_Listbox
Call top30
Range("Q8:AR85").Select
Selection.Copy
Workbook("Results.xls").Open
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Pictures.Paste.Select
ActiveSheet.Shapes.Range(Array("Picture 1")).Select
Next i
End With
End Sub
End If
any help would be appreciated,
Sub ds()
Dim i As Integer
Dim intRowCount As Long
Dim strText As String
Dim aryCharacters As Variant
Dim n As Long
With Sheets("Automate")
intRowCount = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To intRowCount
If Not Cells(i, 1).Value = "" Then
Cells(i, 1).Value = Results.Sheet1.Cells(i, 1).Value 'peptide A
Cells(i, 2).Value = Results.Sheet1.Cells(i, 2).Value 'K position
Cells(i, 3).Value = Results.Sheet1.Cells(i, 3).Value 'peptide B
Cells(i, 4).Value = Results.Sheet1.Cells(i, 4).Value 'K position
'Add peptide A
strText = Replace(Me.Cells(i, 1).Value, Chr(32), vbNullString)
If Len(strText) > 1 Then
If Len(strText) < ThisWorkbook.Worksheets(1).Columns.Count Then
'Optional, clear the row first:
Rows(3).ClearContents
ReDim aryCharacters(1 To 1, 1 To Len(strText))
For n = 1 To Len(strText)
aryCharacters(1, n) = Mid(strText, n, 1)
Next
Select Case Cells(i, 2).Value
Case 1
Set rngDst = Range("AE3")
Case 2
Set rngDst = Range("AD3")
Case 3
Set rngDst = Range("AC3")
Case 4
Set rngDst = Range("AB3")
Case 5
Set rngDst = Range("AA3")
Case 6
Set rngDst = Range("Z3")
Case 7
Set rngDst = Range("Y3")
Case 8
Set rngDst = Range("X3")
Case 9
Set rngDst = Range("W3")
Case 10
Set rngDst = Range("V3")
Case 11
Set rngDst = Range("U3")
Case 12
Set rngDst = Range("T3")
Case 13
Set rngDst = Range("S3")
Case 14
Set rngDst = Range("R3")
Case 15
Set rngDst = Range("Q3")
Case 16
Set rngDst = Range("P3")
Case 17
Set rngDst = Range("O3")
Case 18
Set rngDst = Range("N3")
Case 19
Set rngDst = Range("M3")
Case 20
Set rngDst = Range("L3")
Case 21
Set rngDst = Range("K3")
Case 22
Set rngDst = Range("J3")
Case 23
Set rngDst = Range("I3")
Case 24
Set rngDst = Range("H3")
Case 25
Set rngDst = Range("G3")
Case 26
Set rngDst = Range("F3")
Case 27
Set rngDst = Range("E3")
Case 28
Set rngDst = Range("D3")
Case 29
Set rngDst = Range("C3")
Case 30
Set rngDst = Range("B3")
End Select
rngDst.Resize(, UBound(aryCharacters, 2)).Value = aryCharacters
Else
MsgBox "Too long...", 0, vbNullString
End If
Else
MsgBox "Peptide sequence (A) and Crosslink position are required", _
vbOKOnly + vbCritical, "Warning"
End If
Cells(42, 33).Value = Cells(i, 5).Value 'precursor
Call Show_MGF_Listbox
Call top30
Range("Q8:AR85").Select
Selection.Copy
Workbook("Results.xls").Open
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Pictures.Paste.Select
ActiveSheet.Shapes.Range(Array("Picture 1")).Select
Next i
End With
End Sub
End If