Results 1 to 4 of 4

value return is not on proper header

Threaded View

  1. 03-19-2016, 06:09 AM #1
    darksquare
    darksquare is offline
    Registered User
    Join Date
    03-08-2016
    Location
    manila,philippines
    MS-Off Ver
    excel 2007
    Posts
    11

    value return is not on proper header

    Can't get enough idea, I am enhancing my VBA knowledge but I am stuck in this code
    Sub Editme_Click()
'declare the variables
 Dim findvalue As Range
 Dim cNum As Integer
 Dim DataSH As Worksheet
 'error handling
 On Error GoTo errHandler:
 'hold in memory and stop screen flicker
 Application.ScreenUpdating = False
 Set DataSH = Sheet3
 
 'check for values
 If Sheet6.Range("G22").Value = "" Or Sheet6.Range("G23").Value = "" Then
 MsgBox "There is not data to edit"
 Exit Sub
 End If
 
 
 Set findvalue = DataSH.Range("B:B"). _
 Find(What:=Sheet6.Range("I20").Value, LookIn:=xlValues, LookAt:=xlWhole)
'update the values
 findvalue = Sheet6.Range("I20").Value

findvalue.Offset(0, 1) = Sheet6.Range("G22").Value
findvalue.Offset(0, 2) = Sheet6.Range("G23").Value
findvalue.Offset(0, 3) = Sheet6.Range("G24").Value
findvalue.Offset(0, 4) = Sheet6.Range("G25").Value
findvalue.Offset(0, 5) = Sheet6.Range("M22").Value
findvalue.Offset(0, 6) = Sheet6.Range("G28").Value
findvalue.Offset(0, 7) = Sheet6.Range("G27").Value
findvalue.Offset(0, 8) = Sheet6.Range("M23").Value
findvalue.Offset(0, 9) = Sheet6.Range("O28").Value
findvalue.Offset(0, 10) = Sheet6.Range("O27").Value
findvalue.Offset(0, 10) = Sheet6.Range("M24").Value
findvalue.Offset(0, 11) = Sheet6.Range("J25").Value
findvalue.Offset(0, 12) = Sheet6.Range("P24").Value
findvalue.Offset(0, 13) = Sheet6.Range("P22").Value
findvalue.Offset(0, 14) = Sheet6.Range("P23").Value
findvalue.Offset(0, 15) = Sheet6.Range("P32").Value
findvalue.Offset(0, 16) = Sheet6.Range("E32").Value
findvalue.Offset(0, 17) = Sheet6.Range("M32").Value
findvalue.Offset(0, 18) = Sheet6.Range("H29").Value
findvalue.Offset(0, 19) = Sheet6.Range("O29").Value
findvalue.Offset(0, 25) = Sheet6.Range("E19").Value
findvalue.Offset(0, 28) = Sheet6.Range("H44").Value
findvalue.Offset(0, 29) = Sheet6.Range("HO42").Value
findvalue.Offset(0, 30) = Sheet5.Range("H58")
findvalue.Offset(0, 31) = Sheet5.Range("L58").Value
findvalue.Offset(0, 32) = Sheet5.Range("G59").Value
findvalue.Offset(0, 33) = Sheet5.Range("G60").Value
findvalue.Offset(0, 34) = Sheet5.Range("N60").Value
findvalue.Offset(0, 35) = Sheet6.Range("N12").Value
findvalue.Offset(0, 36) = Sheet6.Range("N13").Value
findvalue.Offset(0, 37) = Sheet6.Range("N15").Value
findvalue.Offset(0, 38) = Sheet6.Range("N16").Value
findvalue.Offset(0, 39) = Sheet6.Range("N18").Value
findvalue.Offset(0, 40) = Sheet6.Range("N19").Value
'findvalue.Offset(0, 41)  'Sheet6.Range("L49").Value
findvalue.Offset(0, 42) = Sheet6.Range("E49").Value
findvalue.Offset(0, 43) = Sheet6.Range("L50").Value
findvalue.Offset(0, 44) = Sheet6.Range("O49").Value
findvalue.Offset(0, 45) = Sheet6.Range("Q49").Value
findvalue.Offset(0, 46) = Sheet6.Range("E50").Value
findvalue.Offset(0, 47) = Sheet6.Range("L50").Value
findvalue.Offset(0, 48) = Sheet6.Range("O50").Value
findvalue.Offset(0, 49) = Sheet6.Range("Q50").Value
findvalue.Offset(0, 50) = Sheet6.Range("E51").Value
findvalue.Offset(0, 51) = Sheet6.Range("L51").Value
findvalue.Offset(0, 52) = Sheet6.Range("O51").Value
findvalue.Offset(0, 53) = Sheet6.Range("Q51").Value 
findvalue.Offset(0, 54) = Sheet6.Range("E41").Value
findvalue.Offset(0, 55) = Sheet6.Range("E42").Value
findvalue.Offset(0, 56) = Sheet6.Range("E43").Value
findvalue.Offset(0, 57) = Sheet6.Range("K41").Value
findvalue.Offset(0, 58) = Sheet6.Range("K42").Value
findvalue.Offset(0, 59) = Sheet6.Range("K43").Value
findvalue.Offset(0, 60) = Sheet6.Range("O41").Value
findvalue.Offset(0, 61) = Sheet6.Range("O43").Value
findvalue.Offset(0, 62) = Sheet6.Range("E35").Value
findvalue.Offset(0, 63) = Sheet6.Range("E36").Value
findvalue.Offset(0, 64) = Sheet6.Range("E37").Value
findvalue.Offset(0, 65) = Sheet6.Range("E38").Value
findvalue.Offset(0, 66) = Sheet6.Range("E39").Value
findvalue.Offset(0, 67) = Sheet6.Range("M35").Value
findvalue.Offset(0, 68) = Sheet6.Range("M36").Value
findvalue.Offset(0, 69) = Sheet6.Range("M37").Value
findvalue.Offset(0, 70) = Sheet6.Range("M38").Value
findvalue.Offset(0, 71) = Sheet6.Range("M39").Value
findvalue.Offset(0, 72) = Sheet6.Range("P35").Value
findvalue.Offset(0, 73) = Sheet6.Range("P36").Value
findvalue.Offset(0, 74) = Sheet6.Range("P37").Value
findvalue.Offset(0, 75) = Sheet6.Range("P38").Value
findvalue.Offset(0, 76) = Sheet6.Range("P39").Value
findvalue.Offset(0, 77) = Sheet6.Range("E13").Value
findvalue.Offset(0, 78) = Sheet6.Range("E14").Value
findvalue.Offset(0, 79) = Sheet6.Range("E15").Value
findvalue.Offset(0, 80) = Sheet6.Range("E16").Value
findvalue.Offset(0, 81) = Sheet5.Range("E21").Value
findvalue.Offset(0, 82) = Sheet5.Range("E22").Value
findvalue.Offset(0, 83) = Sheet5.Range("E23").Value
findvalue.Offset(0, 84) = Sheet5.Range("E24").Value
findvalue.Offset(0, 85) = Sheet5.Range("E25").Value
findvalue.Offset(0, 86) = Sheet5.Range("I21").Value
findvalue.Offset(0, 87) = Sheet5.Range("I22").Value
findvalue.Offset(0, 88) = Sheet5.Range("I23").Value
findvalue.Offset(0, 89) = Sheet5.Range("I24").Value
findvalue.Offset(0, 90) = Sheet5.Range("I25").Value
findvalue.Offset(0, 91) = Sheet5.Range("L21").Value
findvalue.Offset(0, 92) = Sheet5.Range("L22").Value
findvalue.Offset(0, 93) = Sheet5.Range("L23").Value
findvalue.Offset(0, 94) = Sheet5.Range("L24").Value
findvalue.Offset(0, 95) = Sheet5.Range("E30").Value
findvalue.Offset(0, 96) = Sheet5.Range("E31").Value
findvalue.Offset(0, 97) = Sheet5.Range("E32").Value
findvalue.Offset(0, 98) = Sheet5.Range("I30").Value
findvalue.Offset(0, 99) = Sheet5.Range("I31").Value
findvalue.Offset(0, 100) = Sheet5.Range("I32").Value
findvalue.Offset(0, 101) = Sheet5.Range("L30").Value
findvalue.Offset(0, 102) = Sheet5.Range("L31").Value
findvalue.Offset(0, 103) = Sheet5.Range("L32").Value
findvalue.Offset(0, 104) = Sheet5.Range("E35").Value
findvalue.Offset(0, 105) = Sheet5.Range("E36").Value
findvalue.Offset(0, 106) = Sheet5.Range("E38").Value
findvalue.Offset(0, 107) = Sheet5.Range("I35").Value
findvalue.Offset(0, 108) = Sheet5.Range("I36").Value
findvalue.Offset(0, 109) = Sheet5.Range("I37").Value
findvalue.Offset(0, 110) = Sheet5.Range("I38").Value
findvalue.Offset(0, 111) = Sheet5.Range("L35").Value
findvalue.Offset(0, 112) = Sheet5.Range("L37").Value
findvalue.Offset(0, 113) = Sheet5.Range("L38").Value
findvalue.Offset(0, 114) = Sheet5.Range("E41").Value
findvalue.Offset(0, 115) = Sheet5.Range("E42").Value
findvalue.Offset(0, 116) = Sheet5.Range("I41").Value
findvalue.Offset(0, 117) = Sheet5.Range("I42").Value
findvalue.Offset(0, 118) = Sheet5.Range("L41").Value
findvalue.Offset(0, 119) = Sheet5.Range("E45").Value
findvalue.Offset(0, 120) = Sheet5.Range("E46").Value
findvalue.Offset(0, 121) = Sheet5.Range("E47").Value
findvalue.Offset(0, 122) = Sheet5.Range("E48").Value
findvalue.Offset(0, 123) = Sheet5.Range("I45").Value
findvalue.Offset(0, 124) = Sheet5.Range("I46").Value
findvalue.Offset(0, 125) = Sheet5.Range("I47").Value
findvalue.Offset(0, 126) = Sheet5.Range("I48").Value
findvalue.Offset(0, 127) = Sheet5.Range("L45").Value
findvalue.Offset(0, 128) = Sheet5.Range("L46").Value
findvalue.Offset(0, 129) = Sheet5.Range("L47").Value
findvalue.Offset(0, 130) = Sheet5.Range("L48").Value
findvalue.Offset(0, 131) = Sheet5.Range("E51").Value
findvalue.Offset(0, 132) = Sheet5.Range("E52").Value
findvalue.Offset(0, 133) = Sheet5.Range("E53").Value
findvalue.Offset(0, 134) = Sheet5.Range("I51").Value
findvalue.Offset(0, 135) = Sheet5.Range("I52").Value
findvalue.Offset(0, 136) = Sheet5.Range("I53").Value
findvalue.Offset(0, 137) = Sheet5.Range("L51").Value

findvalue.Offset(0, 138) = Sheet5.Range("E55").Value
findvalue.Offset(0, 139) = Sheet5.Range("I55").Value
findvalue.Offset(0, 140) = Sheet6.Range("J48").Value
findvalue.Offset(0, 141) = Sheet6.Range("K48").Value
findvalue.Offset(0, 142) = Sheet5.Range("E56").Value
findvalue.Offset(0, 143) = Sheet5.Range("E57").Value
findvalue.Offset(0, 144) = Sheet6.Range("L14").Value
findvalue.Offset(0, 145) = Sheet6.Range("L17").Value
findvalue.Offset(0, 146) = Sheet6.Range("L20").Value
findvalue.Offset(0, 147) = Sheet6.Range("E26").Value


 'filter the data
 DataSH.Range("B9").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
 CriteriaRange:=Range("Database!$FF$8:$FF$9"), CopyToRange:=Range("Database!$FH$8:$LC$8"), _
 Unique:=False
 


 
   MsgBox "Your data was successfully edited!"

    else
   
        MsgBox ("Incorrect password!")
  
 
 'error block
 On Error GoTo 0
 Exit Sub
errHandler:
 'Protect all sheets
 'Protect_All
 'show error information in a messagebox
 MsgBox "An Error has Occurred " & vbCrLf & _
 "The error number is: " & Err.Number & vbCrLf & _
 Err.Description & vbCrLf & "Please notify the administrator"
 End If
End Sub
    Last edited by alansidman; 03-19-2016 at 09:55 AM.
    Reply With QuoteRegister To Reply
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. INDEX MATCH 2 way won't return proper value
    By sheeptape in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-09-2015, 03:44 PM
  2. [SOLVED] Find specific value in table and return header row and header column value
    By nelwan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-30-2013, 01:35 AM
  3. [SOLVED] Proper way to return to beginning of sub?
    By smaier69 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-14-2013, 08:44 PM
  4. Using Lookup to Return Column Header or Index Function to return revised pay rate
    By lovesexcel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-13-2013, 12:38 PM
  5. Double Lookup to return proper value
    By Nomad33 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-23-2013, 10:22 AM
  6. Retaining proper pivot header of dynamic single column range
    By VTHokie11 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-26-2011, 03:03 PM
  7. LOOKUP function doesn't return proper values
    By dhd2005 in forum Excel General
    Replies: 5
    Last Post: 04-23-2010, 09:21 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1