# excel: macro problems



## PyRoMaNIN (Aug 16, 2006)

I'm trying to create a macro that will add a new line at a certain point on the next sheet of a workbook, and then pastes information into that new line, when sb presses a button.

Until now I only ever did macros by recording them, and then assigning them to a commandbutton, and had no problems with that;
Now I keep gettting an error message telling me there is a bug. And that assistant which pops up marks the _Rows("38:38").Select_ as wrong- but as i have no knowledge of VB I don't really see what's wrong with it.

Private Sub CommandButton1_Click()
Sheets("Lizenzmeldungen").Select
Rows("38:38").Select
Selection.Insert Shift:=xlDown
Sheets("Dateneingabe").Select
End Sub

Also, it'd be great to have something like "find the line where it says X, and put the new row right below that"; if you got any suggestions for that please let me know.

Thanks!


----------



## Jimmy the Hand (Jul 28, 2006)

Try this:


```
Sub InsertRow()
    Sheets("sheet1").Activate
    Columns(1).Select
    Cells(1, 1).Activate
    Selection.Find(What:="X", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Rows(ActiveCell.Row + 1).Select
    Selection.Insert Shift:=xlDown
    Sheets("Sheet2").Select
End Sub
```
Sheet1 and Sheet2 are worksheet names, "X" is the string you are looking for. Replace them with the proper thing.
Also, you can fiddle with the other parameters of Find, their meanings are more or less evident.
This macro searches column(1), that is, column("A") for the value "X". In the 2nd line you can change this.
Instead of Rows("38:38") I would use Row(38), if only one row is to be selected.


----------



## PyRoMaNIN (Aug 16, 2006)

thanks a lot!

i'm slowly but surely figuring out how to adapt this to what i need. This bit is working fine now, but there's still a few things that I haven't quite figured out; but i guess I'm on the right track now.

How can i point the "X" to a cell? I tried putting "cell(1,1)" into the place where x is now, but it doesn't seem to like this. 
Or: can you point me to a VB tutorial or FAQ where I can look up these kind of things myself?


----------



## Jimmy the Hand (Jul 28, 2006)

PyRoMaNIN said:


> How can i point the "X" to a cell? I tried putting "cell(1,1)" into the place where x is now, but it doesn't seem to like this.


"X", as anything between two quotation marks, is a string. You have to replace it with the cell's content, like

```
Selection.Find(What:=Cells(1,1).Value, After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
```
Better, if you use a variable, ant put it into the Find procedure.

```
Dim SeekWhat as String
SeekWhat = ActiveSheet.Cells(1,1).Value
Selection.Find(What:=SeekWhat, After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
```



PyRoMaNIN said:


> Or: can you point me to a VB tutorial or FAQ where I can look up these kind of things myself?


Well, no. I, myself, learned practically everything from the built-in help. Another valuable source of knowledge is the code of recorded macros. But surely there are great Excel sites out there...


----------



## OBP (Mar 8, 2005)

You can replace the "x" with a varaible which has looked up the value in cell(1,1).
i.e.
searchstring = cell(1,1)
and then replae the "x" with searchstring, but do not use inverted commas.


----------



## OBP (Mar 8, 2005)

sorry Malacka, I didn't see your post, I was too busy typing (slowly).


----------



## Jimmy the Hand (Jul 28, 2006)

OBP said:


> sorry Malacka, I didn't see your post, I was too busy typing (slowly).


No problem, I always fall into the same trap  
Only I type more slowly than you, because I have to translate my thoughts first


----------



## PyRoMaNIN (Aug 16, 2006)

Hello...

thanks for the fast reply 
and sorry for bothering you guys again 

What works: 
finding the right place in sheet2 & inserting a new blank line

What i tried to do now: 
copy the content of a cell in sheet1 into the first cell of the new row. as i didn't know how to tell it "go back to that new line" i told it what to copy (cell 6,3) right in the beginning and once it made the new line, insert it there.
Now, instead of inserting it into the first cell there, it inserts it into _ALL_ cells of that row. And it gives me an error message, marking the "selection.paste" as wrong.

My next goal is to make it copy the formulas used in the line below into the new line- which should be easy to figure out by myself once i know why my method of pasting doesn't work... I guess.

so here's my dysfunctional code:


```
Sub neuesProdukt()
    Cells(6, 3).Select
    Selection.Copy
    SeekWhat = ActiveSheet.Cells(6, 2).Value
    Sheets("Lizenzmeldungen").Activate
    Columns(1).Select
    Cells(1, 1).Activate
    Selection.Find(What:=SeekWhat, After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Rows(ActiveCell.Row + 1).Select
    Selection.Insert Shift:=xlDown
    Selection.Interior.ColorIndex = xlNone
    Columns(1).Select
    Selection.Paste
    Application.CutCopyMode = False
End Sub
```
sorry again for bothering you


----------



## Jimmy the Hand (Jul 28, 2006)

No need to apologize, it's my pleasure to help you. Really.  
Doubly so, if I can beat OBP by replying first 

You can refer to *any* cells the following way:

```
WorkBooks("wbkname").Sheets("Shtname").Cells(y,x)
```
where y and x are rowindex and columnindex, respectively.
WorkBooks is optional, by default it is the active workbook (the xls file).
If the cell is on the currently active sheet, you can use 

```
ActiveSheet.Cells(y,x)
```
If you want to copy its content, use a variable

```
CellContent = Sheets("Sheet1").Cells(y1,x1).Value
Sheets("Sheet2").Cells(y2,x2).Value= CellContent
```
If you want to copy the cells formula, use "Formula" instead of "Value". 
You can also omit using variable, and simply transferring the cells content in one step.

So after all this, the macro should be like this:

```
Sub neuesProdukt()
    SourceSheet=ActiveSheet.Name
'    Cells(6, 3).Select
'    Selection.Copy
    SeekWhat = Sheets(SourceSheet).Cells(6, 2).Value
    Sheets("Lizenzmeldungen").Activate
    Columns(1).Select
    Cells(1, 1).Activate
    Selection.Find(What:=SeekWhat, After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Rows(ActiveCell.Row + 1).Select
    Selection.Insert Shift:=xlDown
    Selection.Interior.ColorIndex = xlNone
    SelectedRow=Selection.Row
    ActiveSheet.Cells(SelectedRow,1).Value = Sheets(SourceSheet).Cells(6,3).Value
'    Columns(1).Select
'    Selection.Paste
'    Application.CutCopyMode = False
End Sub
```
I left in the unneccessary lines, and signed them as comments (starting with ' ).


----------

