Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special, Values only
I have part of the code here, I want to include a paste special to get only
the value of cell H3, not the formula. The value comes from a lookup function in the sheet "Summary" and I want to paste the value in to another worksheet, "Pedigrees." Call InsertData("Pedigrees", "N", GetData("Summary", "H3")) Thanks!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special, Values only
You'll need to show the code that's in Sub InsertData. Showing the call
doesn't help much. -- HTH, Barb Reinhardt "EmmieLou" wrote: I have part of the code here, I want to include a paste special to get only the value of cell H3, not the formula. The value comes from a lookup function in the sheet "Summary" and I want to paste the value in to another worksheet, "Pedigrees." Call InsertData("Pedigrees", "N", GetData("Summary", "H3")) Thanks!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special, Values only
Whoops, Sorry about that. Feels like Monday!
Function GetSurveyRow(ByVal PedigreeID As Integer) As Integer Dim RowIndex As Integer Dim MaxSurveyID As Integer Dim CurrentSurveyID As String MaxSurveyID = 0 For RowIndex = 2 To MAX_ROW Sheets("Surveys").Select Range("A" & RowIndex).Select Range("A" & RowIndex).Select CurrentSurveyID = ActiveCell.FormulaR1C1 & "" If CurrentSurveyID = "" Then 'This is the end of the list = Insert new survey Call InsertSurvey(MaxSurveyID + 1, PedigreeID) GetSurveyRow = MaxSurveyID + 1 Exit For Else If CInt(CurrentSurveyID) MaxSurveyID Then MaxSurveyID = CInt(CurrentSurveyID) End If End If Next End Function "EmmieLou" wrote: I have part of the code here, I want to include a paste special to get only the value of cell H3, not the formula. The value comes from a lookup function in the sheet "Summary" and I want to paste the value in to another worksheet, "Pedigrees." Call InsertData("Pedigrees", "N", GetData("Summary", "H3")) Thanks!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special, Values only
Sub InsertPedigree(ByVal NewPedigreeID As Integer)
Call InsertData("Pedigrees", "A", NewPedigreeID) Call InsertData("Pedigrees", "B", "Arizona CBM") Call InsertData("Pedigrees", "C", "State") Call InsertData("Pedigrees", "D", "BCS") Call InsertData("Pedigrees", "E", "Year") Call InsertData("Pedigrees", "F", "Plot") Call InsertData("Pedigrees", "M", "Arizona") Call InsertData("Pedigrees", "N", GetData("Summary", "H3")) Call InsertData("Pedigrees", "O", GetData("Summary", "B3")) Call InsertData("Pedigrees", "P", GetData("Summary", "A3")) End Sub "EmmieLou" wrote: Whoops, Sorry about that. Feels like Monday! Function GetSurveyRow(ByVal PedigreeID As Integer) As Integer Dim RowIndex As Integer Dim MaxSurveyID As Integer Dim CurrentSurveyID As String MaxSurveyID = 0 For RowIndex = 2 To MAX_ROW Sheets("Surveys").Select Range("A" & RowIndex).Select Range("A" & RowIndex).Select CurrentSurveyID = ActiveCell.FormulaR1C1 & "" If CurrentSurveyID = "" Then 'This is the end of the list = Insert new survey Call InsertSurvey(MaxSurveyID + 1, PedigreeID) GetSurveyRow = MaxSurveyID + 1 Exit For Else If CInt(CurrentSurveyID) MaxSurveyID Then MaxSurveyID = CInt(CurrentSurveyID) End If End If Next End Function "EmmieLou" wrote: I have part of the code here, I want to include a paste special to get only the value of cell H3, not the formula. The value comes from a lookup function in the sheet "Summary" and I want to paste the value in to another worksheet, "Pedigrees." Call InsertData("Pedigrees", "N", GetData("Summary", "H3")) Thanks!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special, Values only
Sub InsertData(ByVal SheetName As String, ByVal Column As String, ByVal Value As String) Dim RowIndex As Integer Sheets(SheetName).Select 'Find first empty row For RowIndex = 2 To MAX_ROW Range("A" & RowIndex).Select If ActiveCell.FormulaR1C1 = "" Then If Column < "A" Then RowIndex = RowIndex - 1 'Go back one row if we are not inserting data into the first field Range(Column & RowIndex).Select ActiveCell.FormulaR1C1 = Value Exit For End If Next End Sub "EmmieLou" wrote: Whoops, Sorry about that. Feels like Monday! Function GetSurveyRow(ByVal PedigreeID As Integer) As Integer Dim RowIndex As Integer Dim MaxSurveyID As Integer Dim CurrentSurveyID As String MaxSurveyID = 0 For RowIndex = 2 To MAX_ROW Sheets("Surveys").Select Range("A" & RowIndex).Select Range("A" & RowIndex).Select CurrentSurveyID = ActiveCell.FormulaR1C1 & "" If CurrentSurveyID = "" Then 'This is the end of the list = Insert new survey Call InsertSurvey(MaxSurveyID + 1, PedigreeID) GetSurveyRow = MaxSurveyID + 1 Exit For Else If CInt(CurrentSurveyID) MaxSurveyID Then MaxSurveyID = CInt(CurrentSurveyID) End If End If Next End Function "EmmieLou" wrote: I have part of the code here, I want to include a paste special to get only the value of cell H3, not the formula. The value comes from a lookup function in the sheet "Summary" and I want to paste the value in to another worksheet, "Pedigrees." Call InsertData("Pedigrees", "N", GetData("Summary", "H3")) Thanks!! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special, Values only
Got It!!!, Thanks Though
"EmmieLou" wrote: I have part of the code here, I want to include a paste special to get only the value of cell H3, not the formula. The value comes from a lookup function in the sheet "Summary" and I want to paste the value in to another worksheet, "Pedigrees." Call InsertData("Pedigrees", "N", GetData("Summary", "H3")) Thanks!! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special, Values only
I think I've cleaned up your code a bit. I like to avoid using SELECT if at
all possible because I find it slows down execution. Try it in a test workbook if you want. Sub InsertData(ByVal SheetName As String, ByVal Column As String, _ ByVal Value As String) Dim RowIndex As Integer Dim myWS As Worksheet Dim myRange As Range Set myWS = Nothing On Error Resume Next Set myWS = Sheets(SheetName) On Error GoTo 0 If myWS Is Nothing Then MsgBox ("There is no sheet of name '" & SheetName & "' in the workbook") Exit Sub End If Set myRange = myWS.Range(Column & "2") If myRange.Column < 1 Then Set myRange = myRange.Offset(-1, 0) End If Do Set myRange = myRange.Offset(1, 0) Loop While Not IsEmpty(myRange) myRange.Value = Value End Sub -- HTH, Barb Reinhardt "EmmieLou" wrote: I have part of the code here, I want to include a paste special to get only the value of cell H3, not the formula. The value comes from a lookup function in the sheet "Summary" and I want to paste the value in to another worksheet, "Pedigrees." Call InsertData("Pedigrees", "N", GetData("Summary", "H3")) Thanks!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste Special (Values) | Excel Programming | |||
Paste Special - Values | Excel Worksheet Functions | |||
Paste Special values only | Excel Worksheet Functions | |||
paste special values | Excel Worksheet Functions | |||
Dynamic Copy/Paste Special Formulas/Paste Special Values | Excel Programming |