hot get a cell value in varable

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Excel Programming
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - How to copy a range of cells from one sheet to another I want to copy a set of cells, the values, from one sheet to another in VBA. From "RawData" to "Compare" where x is a row, y is a row and z is a column. These will be changing as the code executes. I have tried this: Sheets("Compare").Range(Cells(x, 1), Cells(x, z)) = Sheets("RawData").Range(Cells(y, 1), Cells(y, z)) Compiles, but gives a run time error. So then I tried this: Set rng1 = Sheets("Compare").Range(Cells(x, 1), Cells(x, z)) Set rng2 = Sheets("RawData").Range(Cells(y, 1), Cells(y, z)) rng1.Value = rng2.Value Compiles, but I get a run time error at the first Set. Sure, I can create do loops to copy cell by cell, but surely there is a better way. Why do these methods not work? What is a good method to use? Thx Tag: hot get a cell value in varable Tag: 1014915
  - 2
    - IF Statement I am working with a document that shows pay rate % change for each year for each employee. If they show any number (positive or negative), it means they have had their yearly review. How can I create a total row that shows how many people this past year still need their reviews? I was thinking of using an if statement but I am unsure of how to set it up.Thanks Tag: hot get a cell value in varable Tag: 1014904
  - 3
    - returning a row number Hi, I have a named range "Amount_Local" in a single column. The following line of code will find the last cell before a blank cell, which is what I want. Range("Amount_Local").End(xlDown).Select How can I get it to tell me what row it is? thanks, Harold Tag: hot get a cell value in varable Tag: 1014900
  - 4
    - Bar Graph in Excel 2007 I am using Excel 2007 and am working with a bar graph. I have actual data from Jan. â??06 though Sep â??08. I would like to modify the graph to automatically pick up new months when I add data. Now, each month I edit the data formula to pick up the new month. I tried extending my data range from Oct â??08 to Dec â??09, even though there is no data yet. The graph showed the 15 months without data which is not what I want. Is there a way to make the graph only display the bar if there is data in the data range (not blank)? Thanks, John M Tag: hot get a cell value in varable Tag: 1014893
  - 5
    - Macro Security I have a excel worksheet that has many macros and the worksheet is quite large. I'm not sure what happened, but all of a sudden the worksheet opens and will not run any macros. It has the security level is high. I check the security level and it's on medium. the worksheet used to ask the "enable" or "disable" macro question when opening the worksheet, but all of a sudden it doesn't. And like I said, the security level is medium, not High. When I open my worksheet I can't run the macros!! Help!!! What can I do to force the enable/disable macro dialog box to come up on opening the worksheet? Tag: hot get a cell value in varable Tag: 1014892
  - 6
    - Macros I wrote the following macro and assigned it to a button a few years ago, I don't know under which version of Excel. It still works. I created it by entering commands manualy and having it recorded as a macro. =========== Sub Button9_Click() Application.Goto Reference:="e" Selection.EntireRow.Insert SendKeys "{UP}", True SendKeys "+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}", True SendKeys "+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}", True SendKeys "+{RIGHT}", True SendKeys "^{c}{DOWN}{ENTER}", True Application.Goto Reference:="e" SendKeys "{DOWN}{RIGHT}{RIGHT}", True ActiveCell.FormulaR1C1 = "=RC[-1]+R[-2]C[5]" Application.Goto Reference:="e" SendKeys "{UP}", True End Sub =========== 1) I can run the macro under XP. 2) I cannot run it under Vista. As soon as it hits the first SendKey, it complains about some security breach. 3) I can not rerecird it from scratch under any new Excel (Vista or XP). It doesn't generate SendKeys. It just moves to the final cell. Which is useless because I'd like to get there dynamiccaly based on the orig cell. Any help will be appriciated. EB Tag: hot get a cell value in varable Tag: 1014886
  - 7
    - How to make a VLOOK UP OR HLOOK UP formula I have 7 columns and 3 thousand rows, 6 of the columns will be field with numbers. I need to organize the numbers in the cell by rows, not by columns in ascending order. In addition, I need to be able to locate specific numbers in the rows: For Example: find #38 in all the rows and then mark the 7th column next to to the row with a #1 the row that don't have #38 live in blank. THX. in advance Tag: hot get a cell value in varable Tag: 1014885
  - 8
    - Function to Check if Cell is Highlighted for Change in Shared Work Good afternoon, everyone. I've been banging my head against the wall trying to figure this out for a few days. I am looking to create a shared workbook and utilize the track changes feature. In this workbook I want to write a function that refers to another cell and tells me whether or not that cell is highlighted as being changed (i.e. has the blue triangle in the top left corner). It is extremely important that it refers to whether or not Excel is highlighting it because I want the function result to change if the highlight change date range is changed. I figured out how to have the function check a cell for comments, but since these only look like comment it doesn't work. I'm hoping the VBA language is similar, but for the life of me I can't find how to reference the highlight for change. Any help would be greatly appreciated! Tag: hot get a cell value in varable Tag: 1014876
  - 9
    - How do I set auto-sort for a column in a worksheet? Hi; I need to create a worksheet to keep record of customers. I have the following example: NAME PHONE ADDRESS CONTRACT WK1 WK2 Phil 123-456 Angel 321-654 John 987-630 I need to keep the first row as the titles row, and need the worksheet to automatically sort in alphabetical order based on the name, and to do it everytime a new customer is added to the list. HOW CAN I DO THAT? Regards, FVO Tag: hot get a cell value in varable Tag: 1014875
  - 10
    - Show checkbox checked on open I need help. I have 3 userforms. Userform1 has 2 buttons, one to start a new project and one that edits the project as saved. Which ever button is clicked brings up userform2 which has 5 checkboxes. What I would like to happen: when the edit button is clicked I would like the checkboxes that were checked the last time to show up as checked. The value of the checkboxes are stored on sheet3 in cells c2-c6. Thank you for your time and assistance. Tag: hot get a cell value in varable Tag: 1014846
  - 11
    - ClickMe I've drawn a button - ClickMe on a work sheet and written a little code for it. [I am used to vb code in the Access environment] NOW!! how do I make the button user clickable please? -- Jim Bunton Tag: hot get a cell value in varable Tag: 1014842
  - 12
    - Application.VLookup I'm trying to programmatically insert comments into columns on multiple worksheets in a workbook. Problem is that it works on 2 of the 3 sheets and not the 3rd. There's a VLookup that matches the data in one column on each sheet by looking for a number. But on the 3rd sheet it craps out. I'm desperate for an answer right now...can anyone help? sPayer = CStr(Application.VLookup(Range("C" & iRow).Value, Range("Payer!C:ZZ"), i - 2, False)) Tag: hot get a cell value in varable Tag: 1014840
  - 13
    - Excel 2007 and Named Ranges I encountered a problem in 2007 which I believe is a bug. Just wondering if this is a known issue or not. When we programatically delete a row using rows(X).entirerow.delete we noticed that in 2007, that a named range that was a row beneath the deleted row did not shift up. In 2003 and with insert in both editions and going through the Excel UI, named ranges shifted when rows were inserted/deleted, just programatically deleting rows/columns did not shift the named ranges. Am I missing something or is this a bug? -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 Tag: hot get a cell value in varable Tag: 1014836
  - 14
    - Ribbon Button not Working with my Referenced Workbook I have a workbook named "Global Schedule" that my users open with Excel versions 2003 and 2007. I have a 2003 Add-In that I reference which contains all of the code to manipulate the Global Schedule. If 2003 opens the Global Schedule I have code to add a custom popup menu with some control buttons. These buttons do custom sorts to the Global Schedule. This part works beautifully. If 2007 is used to open the Global Schedule then a 2007 Add-In is only used to change the Ribbon which also contains custom buttons to do my custom sorts. I want these buttons to call the same procedures that are located in the referenced workbook - 2003 Add-In. Can I not call procedures from a referenced workbook in XML? The problem happens when I click the Ribbon control butttons. I get an Error: "Wrong Number of Arguements or Invalid Property Assignment." I checked the XML code in the CustomUI Editor and the buttons onAction has the correct procedure pointing to the 2003 Add-In reference. I'm not sure what this means because my procedures that the control button calls does not require arguements. Does anyone have any ideas? Here is my basic setup: 'GLOBAL SCHEDULE CODE: Private Sub Workbook_Open() If Val(Application.Version) > 11 Then ' open 2007 Add-In which contains XML to change Ribbon Else Call CustomMenuControl(ThisWorkbook) End If End Sub The 2003 Add-In is always open no matter which version of Excel is used because it contains several modules that I reference to manipulate the Global Schedule Data. -- Cheers, Ryan Tag: hot get a cell value in varable Tag: 1014834
  - 15
    - VLOOKUP FUNCTION Is there a way to return the next largest value using a vlookup function when an exact match is not found? As of now, it keeps returning the value that matches or the next smallest value. Tag: hot get a cell value in varable Tag: 1014831
  - 16
    - Excel 97 I just got thrown a random assignment at work of making Excel spreadsheet made in 97 work with Excel 2007. I have ZERO experience with any form of VB (I have a C background). First, is there anyway make Excel 2007 backwards compatible with Excel 97 code. This, of course, would be the simplest solution. Second, if I have to update the code, how should I approach this with no VB experience? Should this require drastic changes, a complete rewrite, or only minor fixes? I know this can vary case-by-case, but I am looking for generalities right now. Tag: hot get a cell value in varable Tag: 1014827
  - 17
    - Copy Formula I have a formula in a cell. The formula is: =150+200 I wish to copy that formula and place it in another workbook as =150+200 and not the resulting value 300. How do I do this? Tag: hot get a cell value in varable Tag: 1014824
  - 18
    - Should an automated version of Excel close? I'm writing an application in C++ which will read a couple of complicated text files and create an Excel worksheet containing data extracted from these files. In the process of testing, I got to the point where the program: - used CoCreateInstance() to create an instance of Excel and obtain an IUnknown interface - used QueryInterface() to obtain an IDispatch interface - used Invoke() to set the Visible property to TRUE At this point I can see an instance of Excel with no workbook visible on the computer. If I let this test version of the program go ahead and call Release() on each of the interfaces which it has obtained, the instance of Excel closes. This I take to be normal behavior of an automated application. After adding code to get an IDispatch interface to the Workbooks object and calling its Add method, the instance of Excel has a workbook containing three worksheets, just as if it had been started by clicking the Excel button. Now, however if the program proceeds to call Release() on each of the interfaces, Excel does not close. I have to manually close it by clicking its close button. Is this normal behavior? I'm concerned that I may have failed to release an interface although I can't see what that interface might be. I know that I have a IUnknown and IDispatch interfaces to the original Excel object, an IDispatch interface to the Workbooks member, and an IDispatch interface to the Workbook returned by the Add method. The program calls Release() for each of these. Is there anything else I should look for? -- Norm To reply, change domain to an adult feline. Tag: hot get a cell value in varable Tag: 1014822
  - 19
    - sum in range meeting criteria hi, i have a value in cell A10. i want to sum numbers in range B1:D8 where the value in A10 value is equal to a value in range A1:A9. how can this be done? thanks in advance, geebee Tag: hot get a cell value in varable Tag: 1014815
  - 20
    - Selective Ribbon miminise Hi... Is it possible to for a macro to disable the ribbon but keep the quick access toolbar off which I want to use my own toolbar? Cheers Gordon... Tag: hot get a cell value in varable Tag: 1014813
  - 21
    - Can VB execute VBA code? My VBA macro runs slowly in Excel 2007. Will Visual Basic compile my VBA macro into a faster application that I can somehow use with my Excel spreadsheet? -- TomThumb Tag: hot get a cell value in varable Tag: 1014811
  - 22
    - lookup returning #N/A i have the formula, =IF(F21="","",INDEX(Densities!O:P,MATCH(F21,Densities!O:O,0),2)) This returns #N/A if the F21 string can not be found It there anyway to get it to return 0 or empty insstead? Tag: hot get a cell value in varable Tag: 1014808
  - 23
    - Excel 2007 Adding Template Sheets and Invalid Name Error (#NAME?) I have a workbook (.xlsm) that gets built by adding in template sheets (.xltx). There are numerous template sheets that can be added depending on the needs of the workbook. Each template sheet refers to values found on the the Master sheet by named ranges. The values on the Master sheet are unique by workbook, therefore the template sheets cannot be linked to the Master template sheet. The formulas on the template sheets use workbook level named ranges to refer to the values on the Master sheet. The ranges on the Master sheet are defined as workbook level named ranges. When I do a Sheet.Add Type:=<path> the template sheet is added to the workbook, however, all the named ranges that refer to the Master sheet show an Invalid Name Error (#NAME?). If I click on a cell with an Invalid Name error and then click in the formula bar and then click on any cell in the workbook, Excel resolves the invalid name. Nothing is changed, just 3 mouse clicks. Any idea why Excel can't resolve the names when the sheet is added? Tag: hot get a cell value in varable Tag: 1014801
  - 24
    - My Workbook is always hidden when I open it? I don't know what I've done to it. I have on open and before save events, but I can't see the problem. I want the document to open visible. Can you have a look and tell me if there's something I'm missing? Private Sub Workbook_Open() Dim wbk1 As Workbook Dim wbk2 As Workbook On Error Resume Next Set wbk1 = Workbooks("Export_Requests.csv") Set wbk2 = Workbooks("Project Summary.xls") If wbk1 Is Nothing Then ChDir "I:\A&T Contracts\000 Utilities\" Set wbk1 = Workbooks.Open("I:\A&T Contracts\000 Utilities\Project Specific\Contract Docs\Integrated Utility Services\Export_Requests.csv") ActiveWindow.Visible = False End If If wbk2 Is Nothing Then ChDir "I:\A&T Contracts\000 Utilities\Project Specific\Contract Docs\Integrated Utility Services\Option E\Payment\" Set wbk4 = Workbooks.Open("I:\A&T Contracts\000 Utilities\Project Specific\Contract Docs\Integrated Utility Services\Option E\Payment\Project Summary.xls") ActiveWindow.Visible = False End If End Sub '******* Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim i As Integer Dim sPath As String Dim sFilename As String Application.ScreenUpdating = False Range("A1").Select 'Backup to Flashdrive msg = "Do you want to save a Backup to your Flashdrive?" Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then On Error Resume Next Set fs = CreateObject("Scripting.FileSystemObject") For i = 67 To 90 Set drspec = fs.GetDrive(Chr(i)) If drspec.drivetype = 1 Then 'MsgBox "Drive " & Chr(i) & " is the removable drive" sPath = Chr(i) & ":\Work\Solutions Folder\IUS MONITOR\" sFilename = "IUS Monitor " & _ Format(DateSerial(Year(Date), Month(Date), _ Day(Date)), "dd MM yy") & ".xls" ans = MsgBox("Save File as " & sFilename & "?") If ans = vbOK Then 'Check Path exists If CreateObject("Scripting.FileSystemobject") _ .folderexists(sPath) = False Then MkDir sPath End If ActiveWorkbook.SaveCopyAs sPath & sFilename End If i = 90 End If Next i Else ActiveWorkbook.Activate End If Application.ScreenUpdating = True End Sub 'Thanks 'Dylan Tag: hot get a cell value in varable Tag: 1014785
  - 25
    - lookup two columns I have two long tables with nanes and numbers next to them Is there a way to look up both tables in the same formula The names colums are C and G The numbers columns are D and H The tables are on a seperate worksheet Densities Looking up what is in cell F21 Tag: hot get a cell value in varable Tag: 1014783
- Next
  - 1
    - Disable selective events In the code below I use Application.EnableEvents = False to prevent the showing of UserForm1 while the code is running. The problem is that it prevents all events from happening. Is there any way to prevent UserForm1 from showing while still allowing other events to happen? Private Sub CommandButton1_Click() Unload End_Of_Month_Disposal Application.EnableEvents = False Application.ScreenUpdating = False ActiveWorkbook.Worksheets("2009 Training").Sort.SortFields.Clear ActiveWorkbook.Worksheets("2009 Training").Sort.SortFields.Add Key:=Range( _ "A3:A5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("2009 Training").Sort .SetRange Range("A2:N5000") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("B2").Select Application.EnableEvents = True Application.ScreenUpdating = True End Sub Tag: hot get a cell value in varable Tag: 1014772
  - 2
    - Lookup not working I am using this macro but I have two problems when it runs 1 - If the text being searched for isnt there the it still returns a value which is wrong 2- If the text being searched for is there but the result fiel is empty it returns a zero i need it to leave the cell empty Thank you "=IF(RC[-2]="""","""",IF(R[-13]C[-2]=""WBM"",LOOKUP(R[11]C[-2],Densities!C[-5],Densities!C[-4]),LOOKUP(RC[-2],Densities!C[-1],Densities!C)))" Range("H21").Select Selection.AutoFill Destination:=Range("H21:H39"), Type:=xlFillDefault Range("H21:H39").Select Range("H21").Select End Sub Tag: hot get a cell value in varable Tag: 1014770
  - 3
    - lookup returns zero or the wrong number "=IF(RC[-2]="""","""",IF(R[-13]C[-2]=""WBM"",LOOKUP(R[11]C[-2],Densities!C[-5],Densities!C[-4]),LOOKUP(RC[-2],Densities!C[-1],Densities!C)))" Range("H21").Select Selection.AutoFill Destination:=Range("H21:H39"), Type:=xlFillDefault Range("H21:H39").Select Range("H21").Select End Sub Tag: hot get a cell value in varable Tag: 1014769
  - 4
    - Error in loading DLL If I open a spreadsheet with a macro and double click on a block and type something then press enter I get the following error Microsoft visual basic error in loading dll. I did check under tools prefrences but none of the library's seems missing. If however I uncheck microsoft office 10.0 object library the spreadsheet works. It only does this on one notebook. Reinstalling office didn't resolve the issue. Any ideas please. Tag: hot get a cell value in varable Tag: 1014767
  - 5
    - Unable to populate a range with a formula Greetings! This one has me stumped. I am trying to populate a range with a formula. The code is: Sub TestSetAC2() Range("AC2:AC10").Formula = "=SetAC2" Exit Sub End Sub Function SetAC2() SetAC2 = 5 End Function First I opened a blank worksheet. When I execute the procedure, I get #Name? thruout the range. The tool tip just to the left of #Name? says that the formula contains unrecognized text. Any help will be greatly appreciated. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick Tag: hot get a cell value in varable Tag: 1014756
  - 6
    - Excel vs Lotus [pop-up screen with directories] I'm not sure how to explain my problem. It took some time to convince a friend to take the step to use Excel and convert his Lotus data files. He's about 90% there (unbelievable but true!). He uses a network at his workplace [drive k:\] where the files are stored in various directories [6 major elements] and subdirectories [12 branches and for each year]. His laptop is updatedand, when not connected to the network, he runs a DOS(!!) program which enables him to run the k-drive files on the laptop. A pop-up screen appears with Lotus icons [major directories] if double-clicked another pop-up screen appears with the sub-directories and if selected opens the specific file. He calls this his file organizer and challenged me that its not possible with Excel. I told him that I don't know how, but I know some guys out there [which is you!!] For what it's worth -The DOS file reads: SUBST K:/D SUBST K:C:\ Any form of assistance would be much appreciated. -- HJN Tag: hot get a cell value in varable Tag: 1014733
  - 7
    - Macro question Hi there, I have two column look like this A 1 2 3 4 B 1 2 C 1 2 3 D 1 2 And I want it to be A 1 A 2 A 3 A 4 B 1 B 2 C 1 C 2 C 3 D 1 D 2 Another word to fill the empty space with the above letter It always start with some letter and number at the top Thanks, Oded Dror Tag: hot get a cell value in varable Tag: 1014723
  - 8
    - To change command button caption Dear All I am in sheet2. I click a custom tool bar item, then it has to goto sheet1 and change command button in sheet1 by name 'alfa" caption from "go to ballast" to "go to cargo" . Have been getting error when using activesheet.shapes.("alfa").caption= "go to cargo" Pls suggest right code n thnks brgds/captgnvr Tag: hot get a cell value in varable Tag: 1014717
  - 9
    - ListBox multi select updating in database Hi I am stumped- I have a populated listbox that I need to be able to use to find my selections in a database and enter a defined date into the first empty cell in their row. I honestly have limited VBA experience but anything would help. Basically how do I edit/manipulate the selections from a listbox once I have them selected? Tag: hot get a cell value in varable Tag: 1014711
  - 10
    - ignoring blanks cells while importing data Hi, I have been importing data from another excel sheet. I use ='C:\pc ranch report\[ca_outputs_yearly.csv]ca_outputs_yearly'!A46 and dragged the cell to let it automatically copy all the data from the other excel sheet. However, The data from the other excel sheet is automatically generated, so it might have different number of columns. That's why I dragged the cells down a few more rows than the original excel file has to leave extra space just in case there might be more columns generated in the next generation. The extra space that I dragged shows 0 or #REF. Can I add something in front of the ='C:\pc ranch report\[ca_outputs_yearly.csv]ca_outputs_yearly'!A46, so the empty cells won't show up as 0 or # Ref?? Thank you so much!!! Tag: hot get a cell value in varable Tag: 1014706
  - 11
    - How to Select Last Row of Sheet Hello, I have a sub & function where I email the First (column headers) and Last Row of the Sheet. (Sheetname=VoiceMailData) The email is working, and copying the data works fine. I just can't select the First and Last Row. I have 12 columns. Here is my code: Set rng = Nothing On Error Resume Next 'Only the visible cells in the selection Set rng = Cells(Rows.Count, "A").End(xlUp).Row 'You can also use a range if you want 'Set rng = Sheets("YourSheet").Range("D4:D12").SpecialCells(xlCellTypeVisible) On Error GoTo 0 Thanks Stephan Tag: hot get a cell value in varable Tag: 1014704
  - 12
    - Hide VBProject while copying Forms & Modules to another workbook Hi, I have an automated application that modifies a number of workbooks by adding some forms and modules to each one, then saves and closes them. During this process, I use Application.visible = false to hide all the opening/closing workbooks so that the user can do other work without focus jumping to Excel. The problem is that for some reason, the Visual Basic window opens at some point and never closes. This defeats the purpose of making the application visible and I don't know why it's opening the window. It also confuses the user running the application because she doesn't know anything about excel. Any ideas on what command is actually making VBA visible? Any ideas on how to make it invisible? This one's a real bear because if I put a "Stop" or Pause in VBA, VBA automatically becomes visible so there's no way for me to see what's tripping the visiblity property. Thanks, MikeZz Here is the basic routine that copies over the forms and modules: Public Sub SendForms() errMaster = "SendForms: Sending Macros" srcWB.VBProject.VBComponents("frmNotFound").Export Filename:=sStr destWb.VBProject.VBComponents.Import Filename:=sStr srcWB.VBProject.VBComponents("StartupForm").Export Filename:=sStr destWb.VBProject.VBComponents.Import Filename:=sStr srcWB.VBProject.VBComponents("ExportCode").Export Filename:=sStr destWb.VBProject.VBComponents.Import Filename:=sStr Kill sStr Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Dim LineNum As Long Const DQUOTE = """" ' one " character Set VBProj = ActiveWorkbook.VBProject Set VBComp = VBProj.VBComponents("ThisWorkbook") Set CodeMod = VBComp.CodeModule With CodeMod LineNum = .CreateEventProc("Open", "Workbook") LineNum = LineNum + 1 .InsertLines LineNum, " ThisWorkbook.Sheets(" & DQUOTE & "VeryHidden" & DQUOTE & ").Visible = xlVeryHidden" LineNum = LineNum + 1 .InsertLines LineNum, " StartupForm.Show" End With End Sub Tag: hot get a cell value in varable Tag: 1014696
  - 13
    - If then else if problem for DA I know this macro doesn't work (bad syntax) and I also know there must be more elegant ways to do this. Kindly help. Sub HideUnusedHurdles() If Range("NumberOfHurdles").Value = 4 Then GoTo 100 Else If Range("NumberOfHurdles").Value = 3 Then Range("HideMonthlyRowsIfOnlyThreeHurdles").Select Selection.EntireRow.Hidden = True Range("HideQtrlyRowsIfOnlyThreeHurdles").Select Selection.EntireRow.Hidden = True Range("HideAnnualRowsIfOnlyThreeHurdles").Select Selection.EntireRow.Hidden = True GoTo 100 Else If Range("NumberOfHurdles").Value = 2 Then Range("HideMonthlyRowsIfOnlyTwoHurdles").Select Selection.EntireRow.Hidden = True Range("HideQtrlyRowsIfOnlyTwoHurdles").Select Selection.EntireRow.Hidden = True Range("HideAnnualRowsIfOnlyTwoHurdles").Select Selection.EntireRow.Hidden = True GoTo 100 Else If Range("NumberOfHurdles").Value = 1 Then Range("HideMonthlyRowsIfOnlyOneHurdle").Select Selection.EntireRow.Hidden = True Range("HideQtrlyRowsIfOnlyOneHurdle").Select Selection.EntireRow.Hidden = True Range("HideAnnualRowsIfOnlyOneHurdle").Select Selection.EntireRow.Hidden = True 100 End If End Sub Tag: hot get a cell value in varable Tag: 1014695
  - 14
    - formatting backcolor on Forms Control Button I was curious how you could color the backcolor of a forms button using VBA. The default is Grey, I would like to color it another color. Thanks -- daniel Tag: hot get a cell value in varable Tag: 1014683
  - 15
    - Multiple (ByVal Target As Range) Cancel each other out, as written My initial test of the paramenter worked out great!! Then I added the other paramaters. When putting a "1" in cell called TR1_AUG, into parameter 1 parameter 2 obviously cancels it out, so that no ranges are hidden. I currently have 5 parameters of which only ONE may be selected at any time. That one selection causes 8 ranges to be hidden. (although more ranges to hide will likely be required) It is imperative that the DE-selection of the given cell does UN-hide any previously hidden ranges. Can this be done in one go? if so, how? OR should it be set up as 5 independant subs? How would that have to be done? Thank-you If Range("TR1_AUG") = 1 Then 'Single Cell Rows.EntireRow.Hidden = False Range("TR2_PAD_S, TR3_FP16_S, TR4_FP26_S, TR5_FP36_S, Disch2_PAD_S, Disch3_FD16_S, Disch4_FD26_S, Disch5_FD36_S").EntireRow.Hidden = True Else Rows.EntireRow.Hidden = False End If If Range("TR2_PAD") = 1 Then Rows.EntireRow.Hidden = False Range("TR1_AUG_S, TR3_FP16_S, TR4_FP26_S, TR5_FP36_S, Disch1_AUG_S, Disch3_FD16_S, Disch4_FD26_S, Disch5_FD36_S").EntireRow.Hidden = True Else Rows.EntireRow.Hidden = False End If If Range("TR3_FP16") = 1 Then Rows.EntireRow.Hidden = False Range("TR1_AUG_S, TR2_PAD_S, TR4_FP26_S, TR5_FP36_S, Disch1_AUG_S, Disch2_PAD_S, Disch4_FD26_S, Disch5_FD36_S").EntireRow.Hidden = True Else Rows.EntireRow.Hidden = False End If Tag: hot get a cell value in varable Tag: 1014674
  - 16
    - Loging users who open a file I like to log the users who open spreadsheets that I have developed. What I have done in the past is to run the code below to record the information that I need. Dim Log As Range 'Set the range for the user log Set Log = Sheet6.Range("A2") 'Find the first empty cell Do Until IsEmpty(Log) Set Log = Log.Offset(1, 0) Loop 'Record user name and time opened ActiveCell.Value = fOSUserName() ActiveCell.Offset(0, 1).Select ActiveCell.Value = Now() 'Save the changes ActiveWorkbook.Save The workbooks I have typically developed are summary in nature and do not take up much more than half a meg. This process would run and there would a very small delay and the user didn't know there was anything happening. (The fact that I capture this data is common knowlege though.) We just upgraded to Excel 2007 and I have started to develop larger workbooks that I want to track. The lag is enough that it will start to cause problems. Is there a better way to do this? Can I write to an un-opened book or a text-file? I am open to any suggestions. Thanks! PJ Tag: hot get a cell value in varable Tag: 1014672
  - 17
    - Microsoft Office Web Component issue I am using OWC11 in my application. My application functionality is to open the Excel workbook. I am able to open Office 2003 (Xml Spreadsheet) document using OWC11's AxSpreadsheet object. But the AxSpreadsheet show's last cell of each spreadsheet as merged column. But my original spreadsheet does not contain any merge on last column. I don't have any idea why this is happening. Can any one had come over this issue? Thanks in advance. Tag: hot get a cell value in varable Tag: 1014669
  - 18
    - How do I do "Range(A1).text" in a XL forumla? I do almost all of my coding in VBA, not Excel, so this one is new to me... I have an external lookup plug-in (BBG) that returns "#N/A" if the lookup fails. I have populated a column in my sheet with these formulas, some of which return #N/A, and others that return numbers. I would like to make a formula that uses the number if it's there, and ignores it if it's #N/A. In code I would simply refer do this: Left(Range(A1).text,1) <> "#" But how do I do this in an XL formula? Maury Tag: hot get a cell value in varable Tag: 1014664
  - 19
    - can I use an array formula to query a source data table? Is it possible to use an array formula to query an excel source data table? How would the criteria work? -- Please contact me by email if you have an answer to this question. I appreciate it. Tag: hot get a cell value in varable Tag: 1014651
  - 20
    - Paste Special Macro Assistance needed Currently I have the following Macro: Sub getvalues() lr = Application.Max(2, Cells(Rows.Count, 1).End(xlUp).Row) 'MsgBox lr Rows("2:" & lr).ClearContents With Worksheets("Master") slr = .Cells(Rows.Count, "c").End(xlUp).Row 'MsgBox slr For i = 6 To slr dlr = Cells(Rows.Count, "a").End(xlUp).Row + 1 ' If .Cells(i, "am") = upheld Then .Rows(i).Copy Rows(dlr) If .Cells(i, "am") = upheld And IsDate(.Cells(i, "aj")) Then .Rows(i).Copy Rows(dlr) Next i End With End Sub The way it currently pulls information is it looks at column AM for any cells with "upheld". It then checks for a date in AJ. If both are met then it copies the row. This works perfectly for certain aspects of my spreadsheets, but I need two things that I can't figure out. The first, how can I change that above macro so that it won't clear or delete any information after its been copied over? Second, how can I change the macro to paste only the values of the cells that its copying from? Thanks in advance for any assistance. Tag: hot get a cell value in varable Tag: 1014644
  - 21
    - circular reference error for simple IF statement This IF statement is giving me a circular reference error: =IF(F1="C",A1,0,IF(B1="CALL",A1,0)) Is it possible to get around the fact that the data for the True statement is in the same field for both IF statements? Or is it something else in this statement that's causing the error. Thanks for any help. Tag: hot get a cell value in varable Tag: 1014643
  - 22
    - Validation for Password protection? Hi, I'm facing a problem regarding the validation of an XLSX file. Programatically, there has to be checked whether the particular XLSX file is password protected or not and throw an exception if pwd protected. The validation is being done throu C#. Thanks in advance, Regards, Mayur M Tag: hot get a cell value in varable Tag: 1014641
  - 23
    - Custom menu created in an xla macro Hello, I created a custom menu with a vba macro in an xla file (excel 2003) but i have a problem : When the code runs, it looks for the macro "mymacro" defined in ".OnAction="mymacro" not in the xla file itself but in the active xls workbook. I do not understand why Thanks for your help Tag: hot get a cell value in varable Tag: 1014635
  - 24
    - Sum + countcolor Is it possible to use the sum formula and Chip Pearson's countcolor function together? I have a range I want to add based on 2 criteria. The first criteria is if there is a value in the cell and the second is if the cell is a specific colour. Tag: hot get a cell value in varable Tag: 1014634
  - 25
    - I just installed Office 2007 and an Ad-In I have is not working. Tag: hot get a cell value in varable Tag: 1014629

excel does not support more than seven nested if statements and i have around
20 unique values on which bases i have to fill other filed . for this i want
to use case statement in visual basic but unable to get the cell value in a
variable if any other option

Top

RE: hot get a cell value in varable by dmoney

dmoney
Tue Oct 07 15:50:03 CDT 2008

you should first consider the data type.

i chose variant as it is most compatable

dim yourval as variant
yourval=range("a1") ---substitute a1 for the cell reference you want passed.

"mayank" wrote:

> excel does not support more than seven nested if statements and i have around
> 20 unique values on which bases i have to fill other filed . for this i want
> to use case statement in visual basic but unable to get the cell value in a
> variable if any other option

Top