variant

  1. P

    VBA strange behavior of comparison result between String and Variant

    Hi friends, the other day, someone posted an issue on the web asking why the statement "i < ActiveSheet.Rows.Count" returns False, in which the variable "i" is declared as a String and is assigned an integer value 3. I have been thinking about this problem for two days and still can't figure out...
  2. U

    Variant Question

    Hi! i'm trying to set a variant to a specific cell on the macro workbook, and retain that throughout my code. for some reason it' showing as empty, even though I have a value in that cell on the macro WB. Any ideas? Set macroWB = ActiveWorkbook Name = macroWB.Range("P10").Value 'set Range...
  3. V

    Variant limit in VBA userforms?

    Hi, im currently building a userform spreadsheet to input data as well as query the selected data but currently having an issue with adding more code to the userform. the code it self is generally identical using match/index worksheet functions, when I add more lines using...
  4. 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( _...
  5. 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...
  6. 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...
  7. 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...
  8. 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...
  9. 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...
  10. 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...
  11. 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 =...
  12. 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...
  13. 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...
  14. 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...
  15. 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...
  16. 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...
  17. 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...
  18. 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...
  19. 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 =...
  20. 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...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top