New answers tagged

0

This is an answer based on @PeterT 's answer. So I kept the main logic of your code : No merging of cells but center across selection A 2 dimensional array Keep Data and formatting separate Removing magic numbers And I changed a few things : removed all .select I noticed that if I apply "xlCenterAcrossSelection" on the whole row it gives the same result ...


3

There are a few things you can do in your coding to improve both the logic and the organization of your application. Your code does work and kudos for already using a memory-based array to speed up the processing of (partially) filling out your date axis. My suggestion solution below does not use Collections, but creates a two-dimensional array for reasons I'...


2

You are using the Double data type for all variables. I think this is a good thing since it should avoid conversions. But it is probably too large for your purpose. From the doc: Double (double-precision floating-point) 8 bytes -1.79769313486231E308 to -4.94065645841247E-324 for negative values Source: Data type summary Perhaps using the single ...


0

I would propose rearranging and simplifying the code down to this: Sub LaunchTMAP() '' let user know the workbook is downloading so they dont think Outlook froze Dim infoBox As New UserForm2 Call infoBox.Show '' open excel, open the workbook, run the subroutine, then close excel Dim xl As New Excel.Application Dim wb As Excel....


2

Use References in place of CreateObject If you include the reference to the Microsoft Scripting Runtime you can reduce the dim and set of objFSO to Dim fso As New Scripting.FileSystemObject to increase readability. Note that i have removed the obj prefix as it is no longer dimmed as an object. Similarly, this can be done with the file as Dim ...


0

Assuming that you always want the data from a single button press to fall on the same row, you can achieve this functionality with let ws.[A1].Offset(row-1).Resize(1,10) = _ Array( _ JobID.Text, _ Surveyor.Text, _ DateBox.Text, _ AddressBox.Text, _ CityBox.Text, _ ...


3

Whenever you find yourself writing a block of nearly identical code, I recommend creating a common function that simplifies and isolates that functionality. The advantage is that you now have a single location for the logic and can easily make a change that is consistent for each instance you need to use that logic. In your case, you are repeatedly finding ...


Top 50 recent answers are included