Excel.VBA move to cell based on other cell

Microsoft Microsoft office excel 2007 ac...
October 3, 2010 at 14:27:38
Specs: Windows 7, 4g
In VBA, I'm having trouble with the following: I would like to move to a cell that is "x" number of cells to the right of the current cell selected. And "x" equals a value in cell 1E

After that cell is selected, I would like for the column that this newly found cell is in and the 200 columns to the right of it, to be deleted.

Thank you!


See More: Excel.VBA move to cell based on other cell

Report •

#1
October 3, 2010 at 20:59:14
re: "In VBA, I'm having trouble with the following..."

When you say you are having trouble, does that mean you have tried some VBA code that is not working?

If so, why not post what you have and we'll see if we can point of the "troublesome" areas.


Report •

#2
October 3, 2010 at 21:44:08
Hi there, thank you for the response. So my train of thought was leading me to use the "OffSet" property... For ex:

ActiveCell.Offset(0,=(x,y))

but putting the range within the offset property doesn't work. Perhaps that's more my question, how could one put a range within the offset property so that the range represents a value in another cell.

My code works all in all (the meat of it below) however there is a feature I am unable to program. Each batch of data that this macro analizes varies in the number of columns that contain data, those columns that COULD but don't have any data (up to 200) have to be deleted or when printing will be a nightmare. what I am able to calculate in VBA (using COUNT function) is the number of columns with data, but I'm unable to delete everything to the right of the data, so I thought telling the active cell to move down to the end will enable me to select a 200 column range to the right and delete it. Any input much appreciated.


Columns("A:A").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$A$700").AutoFilter Field:=1, Criteria1:="<>0", _
Operator:=xlAnd
Range("G1").Select
ActiveCell.FormulaR1C1 = "1"
Range("H1").Select
ActiveCell.FormulaR1C1 = "2"
Range("I1").Select
ActiveCell.FormulaR1C1 = "3"
Range("G1:I1").Select
Selection.AutoFill Destination:=Range("G1:GX1"), Type:=xlFillDefault
Range("G1:GX1").Select
Selection.Copy
Range("HK2").Select
ActiveSheet.Paste
Range("HK4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("HK4").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-212]=R2C[-212],""*"",RC[-212])"
Range("HK4").Select
Selection.Copy
Range("HL4:PB4").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("HJ4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("HK4:PB4").Select
Selection.Copy
Range("HK6:PB700").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("HJ4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("HJ4").Select
Range("A4:F700").Select
Selection.Cut
Range("HE4").Select
ActiveSheet.Paste
Range("HE2").Select
ActiveCell.FormulaR1C1 = "Last Name"
Range("HF2").Select
ActiveCell.FormulaR1C1 = "F.I."
Range("HG2").Select
ActiveCell.FormulaR1C1 = "M.I."
Range("HH2").Select
ActiveCell.FormulaR1C1 = "U#"
Range("HI2").Select
ActiveCell.FormulaR1C1 = "Sec"
Range("HJ2").Select
ActiveCell.FormulaR1C1 = "Form"
Range("HE2:HJ750").Select
Selection.Cut
Range("HD2").Select
ActiveSheet.Paste
Range("HJ2").Select
ActiveCell.FormulaR1C1 = "# Wrong"
Range("HJ4").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(RC[1]:RC[200],""a"")"
ActiveCell.FormulaR1C1 = _
"=COUNTIF(RC[1]:RC[200],""a"")+COUNTIF(RC[1]:RC[200],""b"")+COUNTIF(RC[1]:RC[200],""c"")+COUNTIF(RC[1]:RC[200],""d"")+COUNTIF(RC[1]:RC[200],""e"")+COUNTIF(RC[1]:RC[200],""0"")"
Range("HJ4").Select
Selection.Copy
Range("HJ6:HJ700").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("HD2:PB2").Select
Selection.Font.Bold = True
Range("HD1").Select
Selection.End(xlDown).Select

Here is the


Report •

#3
October 4, 2010 at 04:55:21
I don't have time to look at your code right now, but I'm going to give you a little assignment:

When you record a macro, you end up with very inefficient code that is bulky and hard to read. You should learn how to "clean up" recorded code.

Here's a tip: You rarely have to Select an object to perform a VBA action on it.

For example, all your code that looks like this:

 Range("HH2").Select
  ActiveCell.FormulaR1C1 = "U#"

Can be reduced to this:

 Range("HH2").FormulaR1C1 = "U#"

Imagine how much easier your code would be to read if you got rid of all of your unnecessary "Select" lines.

Second:

If you use the continuation character to split long lines, it's easier to read the code so you don't have to scroll the window - both in the VBA editor and in this forum.

For example:

ActiveCell.FormulaR1C1 = _
"=COUNTIF(RC[1]:RC[200],""a"")+COUNTIF(RC[1]:RC[200],""b"")+" & _
"COUNTIF(RC[1]:RC[200],""c"")+COUNTIF(RC[1]:RC[200],""d"")+ " & _
"COUNTIF(RC[1]:RC[200],""e"")+COUNTIF(RC[1]:RC[200],""0"")"

Lastly, if you use the pre tags found above the text entry box in this forum, you can line your code up with the indents used in the VBA editor. That makes it much easier to read and follow the code

Sub IndentExample
  For rw = 1 to 10
    If re = 5 then
      MsgBox rw
    End If
  Next
End Sub

If you clean up your code and repost it, it would make it much easier for us to read it and help you out.


Report •
Related Solutions


Ask Question