The Office Online Knowledge Base informs me that a VBA Converter Pack must be installed and to do so I must contact support. Why is this VBA Converter I do not understand why Microsoft chooses not to give easily obtained and free support on their trial versions. I certainly see no reason to upgrade. The Readiness Report Creator can scan for VBA macros in Word, Excel, PowerPoint, Outlook, Access, Project, Visio, and Publisher files, for Office versions as far back as Office 2003. It can also scan for certain types of add-ins used with Office.
I am working with an excel file where I have linked it up with 2-3 database files. It is having nearly 120 columns having the data extracted from the existing databases.
Because of the complexity I wish to change the formula used in the cells to Excel VB. How can this be done. If someone can give some example I could try it in the way I wanted. Data in column C4 is extracted by the formula =IF(AND(ISBLANK(A4),ISBLANK(J4)),',VLOOKUP(MID(LEFT(A4,FIND('-',A4&'-',FIND('-',A4&'-')+1)-1),FIND('-',A4&'-')+1,255),Dbtag,2,0)) where in the Vlookup Checks for the charactes between the hyphens (A4) and returns the data required. Can someone guide me with one example to do the same type of activity in VB.
Thanks in advance. File size is more determined by how many cells you have used and how many sheets: storing values in cells takes nearly as much as using formulas, and 5 MB is not that big.
Sub Macro With Range('C4:C1250').FormulaR1C1 = '=String that is generated by recording a macro entering the formula you show in C4'.Value =.Value ' optional to convert to values End With With Range('D4:D1250') FormulaR1C1 = '=String that is generated by recording a macro entering the formula you show in D4'.Value =.Value ' optional to convert to values End With With Range('I4:I1250') FormulaR1C1 = '=String that is generated by recording a macro entering the formula you show in I4'.Value =.Value ' optional to convert to values End With End Sub HTH, Bernie. Actually, it is far easier to use worksheet formulas than to program the same in VBA. What I often do is figure out the best formula(s), record entering it into a cell or range of cells, and then edit the VBA to have code enter the formula into a range (and convert the results to values if I don't want to have the cells re-calc). For example, if you want that formula in column C to match the entries in column A: With Range('C4', Cells(Rows.Count, 1).End(xlUp).Offset(0, 2)).FormulaR1C1 = '=IF(AND(ISBLANK(RC-2),ISBLANK(RC7)),' & ',VLOOKUP(MID(LEFT(RC-2,FIND('-',RC-2&'-',' & 'FIND('-',RC-2&'-')+1)-1),' & 'FIND('-',RC-2&'-')+1,255),Dbtag,2,0))' '.Value =.Value ' optional to convert to values End With HTH, Bernie.
The intension of using VBA is to make the file size smaller. Now I have used all the formulae in cells and the file size is more than 5mb. Actually I am trying to do it in VBA. I wish to put in the macro to get the work done. The formulas used is in cell C4: =IF(ISBLANK(A4),',VLOOKUP(MID(LEFT(A4,FIND('-',A4&'-',FIND('-',A4&'-')+1)-1),FIND('-',A4&'-')+1,255),Dbtag,2,0)) D4: =IF(ISBLANK(A4),',VLOOKUP(MID(LEFT(A4,FIND('-',A4&'-',FIND('-',A4&'-')+1)-1),FIND('-',A4&'-')+1,255),Dbtag,3,0)) I4: IF(ISBLANK(A4),',VLOOKUP(MID(LEFT(A4,FIND('-',A4&'-',FIND('-',A4&'-')+1)-1),FIND('-',A4&'-')+1,255),Dbtag,4,0)) etc. This formula has to be repeated till row 1250. File size is more determined by how many cells you have used and how many sheets: storing values in cells takes nearly as much as using formulas, and 5 MB is not that big.
Sub Macro With Range('C4:C1250').FormulaR1C1 = '=String that is generated by recording a macro entering the formula you show in C4'.Value =.Value ' optional to convert to values End With With Range('D4:D1250') FormulaR1C1 = '=String that is generated by recording a macro entering the formula you show in D4'.Value =.Value ' optional to convert to values End With With Range('I4:I1250') FormulaR1C1 = '=String that is generated by recording a macro entering the formula you show in I4'.Value =.Value ' optional to convert to values End With End Sub HTH, Bernie.
Comments are closed.
|
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |