variant

  1. J

    Modified Rank function?

    I have worked out a UDF which replicates Excel Rank() function. Now, I am trying to modify it such that instead of outputting 1 2 2 4 as the ranks for a set {2,5,5,8} it should output 1 3 3 4; and it should be able to handle any data as per the modified logic. Public Function Rnk( _...
  2. D

    Looping is killing my Excel 365 VBA code!

    Hello. I have written the below code and it works to create the the correct name and place that value in a Word doc Content Control and then add to the 2 tables the correct information. My problem is that it is also adding other lines instead of just the code that matches the column 6 code...
  3. spencer_time

    Best practice questions, regarding variables and array / Array defined by variable

    Hello, I'm working on a large spreadsheet to help me automate crunching data at work. I've decided to put sets of data into array's (is plural of array, just array?) and am unsure if my approach will slow the end product or cause some other undesired effect. I, on a daily basis, compare...
  4. A

    Find value in worksheet, copy to new row of table in second worksheet

    Hi all, I'm new to the forum and VBA, so thanks in advance for the help! This is what I'm trying to do 1) import data from a text file into a data pane that will feed into a dashboard, 2) search for keywords in the data to find cell references, and 3) copy this data into a new row of an...
  5. Nelson78

    Ideas about how to produce reports from a database

    Hello everybody. I've just taken charge of the database you can see in this link. From this db, updated day by day (see column N), I have to produce some reports. https://imgur.com/wHb7VtH First of all I want to specify that my predecessor used to manage the reports with formulas, for this...
  6. R

    Is there a #rows limit to pasting an Array to a Range?

    Hi, All. I am processing some data (1 year, 5 min. average, so 105120 rows) of several data-items. Col A: Time Col B: data Col C: data . . . First the values are stored in 1-D arrays, then some data cleaning is done, then each array is pasted to an Excel-sheet. ArrPI: Variant/Variant(1 to...
  7. R

    Blank Lines in Email Body

    Hello all, I have a code to generate a outlook email based on cells. I can generate the email fine but for some reason I have two blank lines between the email body text and my signature. Another slight problem is the font size. When I define to 11, it shows as 10. I tried to change to 12 and...
  8. B

    Offset

    Good Afternoon Could someone explain (May be missing something) why the print to worksheet is in Column E <colgroup><col width="83" style="width: 62pt;"> <tbody> Sub SplitColA() Dim i& Dim Cola() As Variant Dim dict As Variant Set dict =...
  9. T

    With command

    I have values in cells A1 to B2 on Sheet1. I want to read those values into an array and paste onto Sheet2. Why is it that this works: Dim MyArray() As Variant MyArray() = Sheet1.Cells(1, 1).CurrentRegion.Value Sheet2.Cells(1, 1).CurrentRegion.Delete...
  10. B

    VBA Loop to skip over data

    I am new to VERY new to macro building but I have reworked one that has been used where I work. I need to add a macro to the beginning of the one in use now to delete any rows that has the word "MISCELLANEOUS" in column C but NOT to delete the row if it has the word "CONCENTRATION" is in column...
  11. J

    Runtime 91 in autofilter.sort.sortfields

    I'm running into an issue with a sort command, that I'm sure I'm missing something simple and just overthinking.... any help appreciated figuring out what went wrong. Using Excel 2016. Public Const EnrollmentCol As String = "AF" Sub ImportSort() Dim CompleteWBFullName As...
  12. J

    How to pass UDF with ParamArray to a Range?

    I have two functions from one of the threads in MrExcel forum. Function Mini(ParamArray values() As Variant) As Variant Dim minValue, Value As Variant minValue = values(0) For Each Value In values If Value < minValue Then minValue = Value Next Mini = minValue End Function...
  13. J

    On Error, is skipping, but not posting next in correct row

    If one of the ret(1), ret(2), ret(3) is not found it skips it, but it pastes the next one in that row, rather than skipping; any ideas how to alter the code to achieve this please? Thanks. Option Explicit Sub Get_Prices() Dim Lastrow As Long Dim LastRow2 As Long Dim x...
  14. F

    Sum numbers in cell seperated by a comma

    I have two methods to do this, but not quite working out for me yes. The UDF is great but I don't want to save this workbook with macros. The Evaluate method seems to have to be in the same row. Is there a formula solution? 2, 3, 6, 8 = 19 Function Test(Rng As Range) As Variant Test...
  15. S

    Copy and Paste routine in VBA with two arrays

    Greetings, I have a fairly simple copy and paste subroutine to bring some numbers into an excel spreadsheet. The Sub inserts a row on each sheet, copies from another sheet, and then pastes back into the initial sheet. I have about 30 sheets from which to do this with and am confused as to how...
  16. marreco

    Adapt code to search partial text, case find put result column AT

    Hi. Sub myFilter2()'Coded by RickXL 'https://www.mrexcel.com/forum/excel-questions/917998-wild-card-unable-use-autofilter-array.html Dim dic As Object Dim eleData As Variant Dim eleCrit As Variant Dim arrData As Variant Dim vTst As Variant Set dic =...
  17. Dr. Demento

    Converting all files that can be read by MS Word into TXT files

    The goal is to convert any file that can be read by MS Word and convert it directly (and hopefully quickly) into a text file. I swear I had this working at one point but it's certainly not working anymore. I don't recall where I got the text in blue, but I don't know if I have the syntax wrong...
  18. M

    delete rows with specific text amend current code to pick up upper or lower case

    I have the following code that basically searches column O and deletes any rows that contain the words in the array. Only problem is it wont remove rows where the words have capitals in or vice versa. I have lots of keywords to search for so adding different versions of each word isn't an...
  19. M

    Define Range with Variant

    I'm trying to insert value ex. Canada in a column to be defined by the user (prompt) and based on cell content, column also defined by the user. Here's my try: Dim FinalRow As Long Dim n As Long Dim ColumnOU As Variant Dim ClassifResult As Variant...
  20. M

    Delete cell and move up

    I have code that stores ranges like "D4" and "E4" in variables x and y respectively. However, I run into a bug with the .delete command... Any ideas on how I can fix this? I also want to have excel move that column up where the clear contents took place. Private Sub CommandButton1_Click()...

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top