VBA excel macro for a button that selects named reference from a named range

See also questions close to this topic

• List of multiple dataframes to separate Excel sheets

  I have a list of dataframes of variable length, and I want to convert each list of dataframes to each excel sheet.

  Here is my code:

  for i in range(1, len(dfs)):
      frames = {'sheetName_i' : dfs[i]}
  for sheet, df in frames.items():
      dfs[i].to_excel(writer, index=False)
  #df is the whole dataframe whereas dfs is the list of df
  

  I got only the last list at my output. Is there any way I can convert all the lists to separatel Excel sheets?

  Pleased to hear some suggestions. Thank you very much in advance.
• Excel Insert Formula into Cell Reference to Another Sheet

  How can I insert a formula into cell reference to another sheet

  For example,

  Cell D4 =Analysis!I16 (Analysis Sheet Cell I 16)

  Cell D5 =Analysis!I25 (Analysis Sheet Cell I 25)

  Cell D6 =Analysis!I34 (Analysis Sheet Cell I 34)

  How do I type it as a formula ?

  
• Excel file to dictionary

  I have an excel file which contains 2 columns and I want to convert these columns to the dictionary using the header as key and rows as values.

  file1.xlxs
  
  name      price
  Merry     5000
  John      6500
  Nat       4800
  

  The dictionary should be like this:

  List1 =  {'name':['Merry,John,Nat'],'price':['5000,6500,4800']}
  

  Please help?
• Getting a more useful/accurate error message

  We have an issue with an Access front-end that seemingly randomly but constantly times out users with no explanation. This has been a long-standing issue, each time the disconnect happens we're provided with the sub that's causing the issue and a generic error message.

  I'm starting to think the error message is misleading, it gives a generic "Connection Failure" with an Error Code of 0. After Googling the code that apparently means no error occurred?

  I use C# not VBA so I'm not familiar with it but the error handling is as follows:

      Private Sub Form_Timer()
  
      Dim blnSystemMaintenance As Boolean
  
      On Error GoTo ErrHand
  
      ' Check for System Maintenance.
      blnSystemMaintenance = DLookup("SystemMaintenance", "ConfigItems")
      If blnSystemMaintenance Then
  
      //Do stuff
          Else
      //Do stuff
          End If
  
      //Do stuff
  
      End If
  
      ErrExit:
          Exit Sub
          Resume
      ErrHand:
      '    If Err.Number = 1 Then
  
      '    Else
              MsgBox "Error number: " & Err.Number & vbCrLf & _
             "Error description: " & Err.Description, vbCritical, _
             "Unexpected Error Occurred in Sub: Form_Timer"
      Resume ErrExit
      '    End If
  
  
      End Sub
  

  Is there a way to get a more helpful error message?
• Can't add item to combobox (form control) in vba excel

  I have several ComboBoxes (form control) on sheet1. I would like to loop through and populate them. Nothing happens, no error or no items in the list of each ComboBox. I use this code:

  Sub cmbBox()
      Dim ctrl As Shape
      For Each ctrl In Sheet1.Shapes
          If ctrl.Type = msoOLEControlObject Then
              ctrl.AddItem "Done"
              ctrl.AddItem "Not Done"
          End If
      Next
  End Sub
  
• Input box with default value (excel VBA)

  I tried the InputBox for the "period" which is the month ("m"), and it worked. But now I tried using it for the "year" ("YYYY"), and it does not function as expected.

  Here is my code for the "year" (same as in "period", only different values and variables):

  Dim VYear as variant
  Dim defY as variant
  
  defY = Format(DateAdd("YYYY", 0, Now), "YYYY")
  VYear = InputBox("Year covered","Year",defY)
  If VYear > 2014 And VYear < defY Then
      Range("I1").Value = VYear
  ElseIf VYear = "" Then
      Exit Sub
  Else
      Do Until VYear > 2014 And VYear < defY
      MsgBox "Please enter a year not earlier than 2015 and not later than this year"
      VYear = InputBox("Year covered")
      Loop
  End If
  

  Well, it does give me the default value of 2018, but when I tried entering wrong values, it would proceed with the message in MsgBox (as expected), but it would no longer accept ANY values, even the year today (2018).

  Cycle goes like: MsgBox (Please enter....) then InputBox then MsgBox again...

  What could have caused the error?

  PS. I intentionally used As Variant so that even though users input letters, it won't give the error of "type mismatch".
• got an error 1004: we can do that to a merged cell.. why?

  got an Error 1004: we can do that to a merged cell.. why ? how can I fix it ? I want to copy cell in twb to extwb... this code be like = run -> got an error -> reset macro -> run again -> it work

  Sub Ke1()
  
  Dim twb As Workbook
  Dim extwb As Workbook
  Dim rng As Range
  Dim i As Long
  Dim lastRow As Long
  lastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
  
  
  Set twb = Workbooks.Open("C:\Users\faisal.abraham\Documents\Faisal\Travel\CCCPU030732017.xlsx")
  Set extwb = Workbooks.Open("C:\Users\faisal.abraham\Documents\Faisal\Travel\CCCPU030732018.xlsx")
  'Set twb = Application.Workbooks("CCCPU030732017.xlsx")
  'Set extwb = Application.Workbooks("CCCPU030732018.xlsx")
  
  Set rng = twb.Worksheets("PAID").Range("A1:E500")
  For i = 5 To rng.Cells(Rows.Count, 2).End(xlUp).Row
  
      rng.Cells(i, 1).Copy extwb.Worksheets("PAID").Cells(lastRow, 1)
      rng.Cells(i, 2).Copy extwb.Worksheets("PAID").Cells(lastRow, 2)
      rng.Cells(i, 3).Copy extwb.Worksheets("PAID").Cells(lastRow, 3)
      rng.Cells(i, 4).Copy extwb.Worksheets("PAID").Cells(lastRow, 4)
      rng.Cells(i, 5).Copy extwb.Worksheets("PAID").Cells(lastRow, 5)
  
      lastRow = lastRow + 1
  
  Next i
  
  twb.Close savechanges:=False
  extwb.Saved = True
  
  End Sub
  
• Difficulty with reading stock csv file

  The input file will be a CSV file with each row in the format buy_or_sell,quantity,ticker,date where buy_or_sell is either the string buy or sell, quantity is an integer representing the number of shares traded, ticker is a string representing the ticker symbol being traded, and date is the date of the trade in the format YYYY-MM-DD. You may assume there are files named <ticker>.csv for each ticker symbol that the company trades. Each row of these CSV files will be in the format date,price,volume where date is in the format YYYY-MM-DD, the price is a float, and volume is an integer representing the number of shares that were traded on that day by all traders (you will not need the trading volume for this prelab)

  Write a function named portfolio_value that takes one parameter that is a string representing a filename that contains all the trades made by your company throughout the year and returns the total value of the portfolio on the date 2015-12-31. The total value is the sum of all the quantities of stock owned times the price for that stock at the end of the year.

  import csv
  from datetime import date 
  
  def quantities_owned(filename):
      tran = {'buy': 1, 'sell': -1}
      with open(filename) as f:
          reader = csv.reader(f)
          dicti = {}
          for row in reader:
              if row[2] in dicti:
                  dicti[row[2]] += tran[row[0]] * int(row[1])
              else:
                  dicti[row[2]] = tran[row[0]] * int(row[1])
      return dicti
  
  def read_prices(tickers):
      d = {}
      for ticker in tickers:
          with open(ticker + '.csv') as g:
              for date, price, _ in csv.reader(g):
                  d.setdefault(ticker, {})[date] = float(price)
      return d
  
  def portfolio_value(filename):
      sum = 0
      templist =[]
      with open(filename) as h:
          for line in h:
              templist = line[2]
      info = read_prices(templist)
      quantities = quantities_owned(filename)
      for i in info:
          price = int(info['2015-12-31'])
          sum = sum + price
      for j in quantities:
          quan = int(quantities['2015-12-31'])
      value = sumsum * quan
      return value
  

  I am getting error on input ['trades.csv']: [Errno 2] No such file or directory: 'y.csv' What am I doing wrong?
• Swift: Get localized time display - with separate AM/PM in 12 hours regions

  I would like to display the time of the current date in a UILabel in the current locale. Assuming the current time would be 15:30 (24 hours display) / 3:30 PM (12 hours display). Now, if the current locale of the user is 12 hours display, the label should only show "3:30", and the "PM" should display in a second label. I intended to use

  someDateTime = Date()
  let df = DateFormatter()
  df.locale = Locale.autoupdatingCurrent
  df.timeStyle = .short
  timeLabel.text =  df.string(from: someDateTime) // how without AM/PM ?
  ampmLabel.text = "???" // how to show only in 12 hours regions?
  

  But in 12 hours regions it comes always with the AM/PM attached to it. How could I easily distinguish these cases?
• Create new variable using ifelse statement in R

  I would like to create a new variable using and ifelse statement and the following data:

     ID    Column 1    Column 2    Column 3    Column 4     Column 5 
     Ann        1           Y            A     2000-10-15  2000-10-14
     Ann        1           N            A     2000-10-16  2000-11-14
     Ben        1           Y            B     2000-10-11  2000-11-14
     Ben        2           Y            B     2000-11-13  2000
     Fred       1           N            A     2000-11-11  2000-11-15
     Jenny      1           Y            A     2001-11-12  2000-10-14
     Jenny      1           Y            A     2001-11     2000-11-15
  

  By ID, I would like to to find the last complete date if it fits certain criteria. The criteria would be only select last date if it is 1 in Column 1, and A in Column 3 otherwise leave blank. The second criteria I would like to use is by ID, if Column 3 is B, keep that B date. So it would look like this:

     ID    Column 1      Column 2   Column 3   Column 4   Column 5    Column 6 
     Ann        1           Y            A     2000-10-15  2000-10-14 2000-10-15
     Ann        1           N            A     2000-10-16  2000-11-14
     Ben        1           Y            B     2000-10-11  2000-11-14 2000-11-14
     Ben        2           Y            B     2000-11-13  2000
     Fred       1           N            A     2000-11-11  2000-11-15
     Jenny      1           Y            A     2001-11-12  2000-10-14 2001-11-12
     Jenny      1           Y            A     2001-11     2000-11-15
  

  I believe the if statement would go something like this but there could be a better way. Additionally, my lastcompletedate function isn't complete:

   ifelse(Column 1 == 1 & Column 3 == "Y", lastcompletedate(Column4), 
   ifelse(Column 2 == 'B', lastcompletedate(Column5), "") 
   lastcompletedate <-ddply(df,.(ID), tail,1)